Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
May 09, 2025
Solved

Visual and dax level optimization that causes report slowdown

Hi, I have this visual as attached, that has lot of visual level filters applied it has a dax measure called data completeness as below   Data Completeness = var _total = COUNT('Calendar'[Date])...
admin
BI & Data Analysis
community
developer
excel
Excel on mobile
Formulas and Functions
office 365
power bi
User Adoption
  • SergeiBaklan's avatar
    SergeiBaklan
    May 14, 2025

    Since in matrix you use months only as periods, changed  grouping a bit. To the Data table column is added

    Month = RELATED( 'Calendar'[Month] )

    and new measures are

    Count of Exisitng Days by Month = VAR bydates =
    SUMMARIZECOLUMNS (
        Data[Month],
        "count",
            COUNTROWS (
                DISTINCT ( SELECTCOLUMNS ( Data, Data[Date], Data[DBName-Point_Id] ) )
            )
    )
RETURN
    SUMX ( bydates, [count] )

///
Data Completeness by Month = DIVIDE ( [Count of Exisitng Days by Month], [Count of Possible Days] )

    With them matrix calculation is

    compare to

    Not a lot but at least something. Matrix is on "Page 1" at PR-419 - Data Coverage - 03.pbix

Excellove15's avatar
Excellove15
Iron Contributor
May 10, 2025

Hi SergeiBaklan​  Sir,

 

Many thanks for your quick response!😊 

Thats awesome! if you could attach the link to file so that i can refer how you linked 'related to it table' that replaced the calculated column DS in Data.

 

Many thanks Sir!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 10, 2025

File is here PR-419 - Data Coverage - 01.pbix

But again, these are small things. Small multiplies works more or less good. Main problem is in matrix, but it looks like model shall be rebuild to receive correct result. With current relationships it doesn't calculate correctly and calculation itself takes a lot of time. But I'm not ready to suggest something right now.

Resources