Forum Discussion
Slow switch measure
try running a performance analysis and show here the results, also take the dax from those measure and run them on the dax studio to get a better view of the problem, the analysis could show its a enterily different problem and not the dax measures itself.
also look for the ugly babies: https://www.youtube.com/watch?v=91D1WrNrHtY
Hi StefanoGrimaldi, thank you for your comment! I used the performance analyser and DAX studio to investigate the problem, but I have to say I am not an expert at this, so it did not give me new ideas for improvement;
As regards the video you sent;
- Number of visuals doesn't seem the problem here as the DAX query consumes most time
- We do have a star schema data model
- Perhaps the DAX query could be optimized, but my experiments seem to indicate that the switch function is the bottleneck.
I conducted the following experiments;
| Experiment | Description | DAX | Improvement |
| 0 | Base case, the original measure | VAR VarID = SELECTEDVALUE( 'Dashboard variables'[ID] ) VAR Result = SWITCH( VarID, 1, [SUM Area (Crops)], 2, [SUM SO (Crops)], |
|
| 1 | Replaced switch with one measure | [SUM Area (Crops)] | Factor 10 |
| 2 | Less measures in switch (10 instead of 35) | Factor 3 | |
| 3 | Replaced all measures with 1 except for one | SWITCH( VarID, 1, 1, 2, 1, 3, 1, ... 34, [SUM SOTotaal], etc. | Factor 4 |
| 4 | Replaced all measures with one measure | SWITCH( VarID, 1, [SUM SOTotaal], 2, [SUM SOTotaal], 3, [SUM SOTotaal], ... 34, [SUM SOTotaal], etc. | Factor 2 |
| 5 | Batching of measures | Variable switch:= VAR VarID = SELECTEDVALUE( 'Dashboard variables'[Table] ) VAR Result = SWITCH( VarID, "Animals", [Variable switch animals], "Crops", [Variable switch crops], "BRSDetails", [Variable switch BRSDetails] ) RETURN Result
Variable switch BRSDetails:= VAR VarID = SELECTEDVALUE( 'Dashboard variables'[ID] ) VAR Result = SWITCH( VarID, 33, [N BRS (BRSDetail)], 34, [SUM SOTotaal], 35, [AVG SOTotaal] ) RETURN Result | No improvement |
| 6 | Calculation groups | CALCULATE( SELECTEDMEASURE(), TREATAS( VALUES( 'Dashboard CG Variables'[Measure name] ), 'Dashboard CG Variables Help'[Measure] ) )
| No improvement |
yes indead the problem the switch option here, and thats a pretty sumply dax and seems good, try maybe making agregation table for both sums function that can give you a little boost on the performance for that dax meanwhile.
Yes, I could consider that option. But we have many dimensions as well, so the aggregated tables will still be large, unless I minimize the dimensions to filter on, which would be a large setback...
Hi D0minique ,
Taking into account that you have a large dataset and depending on the calculations you want maybe is good in terms of performance to check the aggregations:
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-aggregations
This can reduce table sizes so you can focus on important data and improve query performance. Aggregations enable interactive analysis over big data in ways that aren't possible otherwise, and can dramatically reduce the cost of unlocking large datasets for decision making
