Forum Discussion
Report is too slow
God help us you have a SUMX with an ALL filter nested inside another SUMX with an ALL filter. I'm not trying to be mean but that's the DAX equivalent of replacing your car's oil with 5-minute epoxy. On every row of your table, your formula evaluates a formula on every row of the table. Inside that formula, on every row of the table, it evaluates another formula on every row of the table. It then uses these results to generate a summary table that includes every row of the table. You see the problem right?
It gets even worse when you put this into a visual. If you're putting this measure next to a column from the table, it must do all those steps a separate time for each member of that column in the visual.
It would help to see some sample data so I can get an idea of what kind of tables this is operating on. You can make something fake at mockaroo.com if your real data cannot be shared. How many rows are in this performance snapshot table? Also you're referencing several other measures inside this measure, so it would also be helpful to see the formulas for those measures.
Right off the bat I can tell you that the general pattern you're using is not as efficient as it could be, aside from the nested SUMX issue. Written generically, your measure follows this pattern:
SUMMARIZE( TableName, TableName[Column1], TableName[Column2], "MeasureName", SUMX( FILTER( TableName, TableName[Column3] = "examplevalue" ), CALCULATE(SUM(TableName[Column4])) ) )
That is almost always less efficient than this pattern:
ADDCOLUMNS( SUMMARIZE( TableName, TableName[Column1], TableName[Column2] ), "MeasureName", SUMX( FILTER( TableName, TableName[Column3] = "examplevalue" ), CALCULATE(SUM(TableName[Column4])) ) )
In your case the speed gain will be pretty minor right now, because the rest of the formula is slowing everything down so much. Still, the final form of this measure should still be switched to this pattern because hopefully we'll find a way to rewrite those nested SUMXs so they're not such a problem. I'll get back to you on the rest of it once I have a little more info from you about how it all works.
Anonymous Hi,
Sorry to bother you, but have you had some time yet to review my response?
Kind regards,
Matt
- Ermin9 years agoAdvocate II
Hi Anonymous,
Did you already resolve the problem?
I have impression that the reason for the performance issues may be he number of slicers (or filters) being used. I have a power Bi Desktop report with a bunch of filters ad dependencies (interactions) between the different visualisation and somehow the performance has dropped "proportionally" to the number of slicers and interactions being used.
Did you investigate anything in that direction?