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

Excellove15

Iron Contributor

May 10, 2025

Hi SergeiBaklan Sir,

The Count of Existing Days works beautifully! 😊

I have made all changes that you suggested.
But this dax is referenced by a dax 'Data Completeness'. So when I referenced the dax you gave above as below:

Data Completeness = 

var _total = COUNT('Calendar'[Date])*COUNT(Points[DBName-Point_Id])
var _result = [Count of Existing Days_New]/_total

return _result

'the performance is still an issue:

Is there any way you can help me to improve the above dax(Data Completeness)?

PFA file here https://1drv.ms/u/c/cfada767f73d87ed/Efl-4uYzkklNjOb1NakC9cEB7B7xXbhHb-w-K3TpvooIjw?e=jxVtOs

Please advise sir!

SergeiBaklan

Diamond Contributor

May 11, 2025

Do you speak about matrix? IMHO, model for it shall be rebuilt.

What depends only on discussed measures that's visual with chart. It is improved a bit. Not dramatically, but better than before. In the file I removed old measures since at one place you had the mix.

PR-419 - Data Coverage - 02.pbix

Excellove15
Iron Contributor
May 12, 2025
Hi SergeiBaklan Sir,

Many thanks and its awesome the performance has improved a lot!😊
this visual works like a gem!
Within a few span of time, appreciate the effort you put in to resolve this kind of big issues which was causing headache to us! Its a amazing work and surely deserves a reward😍
Sorry for confusion and yes, I was talking about this matrix visual below and reason for raising this query is also matrix visual:
' IMHO, model for it shall be rebuilt.'--- It would be greatly appreciated if you can help me rebuilt this or suggest how to do it?
This is the only problem we have and we can close this query, if performance improved slightly.
Many Thanks for attaching file sir!

Thanks in advance Sir!
- Excellove15
  Iron Contributor
  May 12, 2025
  Hi SergeiBaklan Sir,
  
  I know you might be having a busy schedule.
  Just to confirm, any update on the model rebuilt as requested above?
  
  Thanks in advance sir!
  - SergeiBaklan
    Diamond Contributor
    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