Forum Discussion

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])...

Formulas and Functions

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

SergeiBaklan

Diamond Contributor

May 10, 2025

You may slightly improve performance if instead of SUMMARIZE(table, field1, field2) use ADDCOLUMNS, i.e. for Count of Existing dates measure

VAR bydates =
    ADDCOLUMNS(
        SUMMARIZE( Data, Data[Date] ),
        "count",
            CALCULATE(
                COUNTROWS( SUMMARIZE( Data, Data[DBName-Point_Id] ) )
            )
    )
RETURN
    CALCULATE( SUMX( bydates, [count] ) )

Existing measure gives

Updated measure

plus it's more effective on cached data. Use within measure SUMMARIZE, VALUES or DISTINCTCOUNT practically doesn't matter in this case from performance point of view. However, recommendation is

Few other milliseconds gives if to mark Calendar as date table; instead of calculating column in Data use related to it table as

perhaps some other minor things.

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
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.