Forum Discussion
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])*COUNT(Points[DBName-Point_Id])
var _result = [Count of Exisitng Days]/_total
return _result
It references a dax called Count of Exisitng Days as below
Count of Exisitng Days =
Var dates = SUMMARIZE(Data, Data[DBName-Point_Id], Data[Date])
Var Ext_dates = COUNTROWS(dates)
return Ext_dates
Now, the problem I have here is the performance of this visual is causing report to slow down. Please let me know how to optimize the dax and further steps to increase the performance
PFA file here PR-419 - Data Coverage - Copy.pbix
Thanks in advance!
10 Replies
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 PR-419 - Data Coverage - Copy.pbix
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 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!
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.
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_datesDISTINCTCOUNT(Data[Date]) instead of SUMMARIZE() and COUNTROWS(), as it directly counts unique values.
