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

Kidd_Ip's avatar
Kidd_Ip
MVP
May 10, 2025

There are multiple ways to optimize, you may start from Data Completeness measure say instead of calculating _total dynamically with COUNT('Calendar'[Date]) * COUNT(Points[DBName-Point_Id]), consider precomputing these values in a separate table. Count of Existing Days may using

Count of Existing Days =
VAR Ext_dates = DISTINCTCOUNT(Data[Date])
RETURN Ext_dates

DISTINCTCOUNT(Data[Date]) instead of SUMMARIZE() and COUNTROWS(), as it directly counts unique values.

 

Resources