Forum Discussion
Report pages loading delay issue
Hi Anonymous ,
Based on these measures, in all of them you use ALL function and then 2-3 comparisons on a row level.
Which means for each measure it is running through at least 15-25 M rows.
Ways to improve it:
1) it would be benefitial if you could have additional columns in data model (in these largest tables) which would already precalculate MAX(column1), MAX(column2), furthermore to have complete logic in these additional columns (instead of measures).
By doing it via columns, refresh may be a little bit longer, but measures will be much faster for sure.
Or at least try using variables, something like this:
Sellin_MTD_UC =
VAR maxColumn1 = MAX('tablename1'[columnname1])
VAR maxColumn2 = MAX('tablename1'[columnname2])
VAR maxColumn3 = MAX('tablename1'[columnname3])
RETURN
CALCULATE(
[Sellin Unit Cases (UC)],
'tablename1'[columnname1] <= maxColumn1,
'tablename1'[columnname2] = maxColumn2,
'tablename1'[columnname3] = maxColumn3
)
These measures with combination of ALL(table) + MAX conditions are the issue.
nandic Should I create these columns in the power bi as dax calculated columns .
Because my data semantic model mostly is coming as direct query from the data lake .
Data lake is providing some measures created over there as well .
And some measures I am creating in power bi when the ad-hoc files from SharePoint are coming.
Could you help me in this ?
Thanks a lot in advance nandic
Anonymous if these DAX measures above are in your semantic model (dataset), then you should do upgrades in that pbix file.
What i would do:
1) put each measure which contains ALL(Table) + conditions in separate card visual on the same page. Then run performance analyzer to see which is consuming the most dax time
2) then create duplicate of that measure, but using variables as mentioned above. Add this new measure to the new card visual on the page and run performace analyser again. Compare original vs this new measure to see if that has any impact on dax time (and make sure values match, that the logic didn't impact on calculation logic)
3) if it doesn't have any impact, then try adding new columns in the model for Max(column1, column2) or even further full logic if(column1>=max(column2),1,0). And then in measure just use filter(table, columnN =1...)
This is how i would approach the problem. Start from 1 measure which takes a lot of time and try to optimize it.