Forum Discussion
Visual and dax level optimization that causes report slowdown
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
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.
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!
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.
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!
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!