Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I was wondering if somebody has found a working solution for the slow switch statement problem?
For my dashboard I would like to switch between over 100 measures (I call this the base measure). All the measures inside the switch statement are simple column sums and averages. This base measure is used in all kind of other measures, such as YOY calculations. Performance slows down when more measures are added to the switch statement.
I have read multiple posts about this topic, but none of the proposed solutions worked for me unfortunately:
- https://www.reddit.com/r/PowerBI/comments/gaytnc/measure_slow_due_to_weird_switchselectedvalue/
- https://community.powerbi.com/t5/DAX-Commands-and-Tips/Slow-Performance-Switch/m-p/1795454#M37826
- https://community.powerbi.com/t5/Desktop/Slow-Switch-Statement/m-p/1085972#M502954
The power bi report server may 2021 feature summary announced that "A performance improvement has been made to SWITCH function with many branches and deeply nested IF functions to eliminate branches which are not selected by user filters or slicers earlier in the calculation pipeline." (https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-may-2021-feature-summary/#perf). But in my case, performance is still too slow.
In addition, I tried to solve this issue by making use of calculation groups. But the same problem applies here. The larger the number of calculation items, the slower the performance; https://community.powerbi.com/t5/Issues/Calculation-Group-slows-down-as-the-number-of-Calculation-It....
Does anybody have an idea how this issue could be solved? Thanks in advance!
Dominique
-----------------------------------------------------------------------------
Update 6 july 2021:
I would like to thank everyone for helping out 🙂
The solution we chose to implement is dividing the measures over multiple 'switch' measures. Instead of having a single page with many measures, we have multiple copies of the page, each with its own switch measure containing 5 measures at max, using calculation groups to replace the measures in the visuals.
Hi @D0minique ,
What is the purpose of this switch function is to have the calcutions of YOY , YTD and so on calculations?
In this case you should make use of the Calculated groups that allows to have a single measure that replicates it's calculation over a set of other measures.
https://www.sqlbi.com/calculation-groups/
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix , thank you for your suggestion! I have tried this option already following the next example;
https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expre....
Unfortunately, this did not improve the performance. Many calculation group items lead to the same perfomance issues as many measures in the switch statement; https://community.powerbi.com/t5/Issues/Calculation-Group-slows-down-as-the-number-of-Calculation-It....
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
Proud to be a Super User!
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.
Proud to be a Super User!
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...
but the agregation table dont related to the dimensional , when you set a aggregation its only linked to the fact table it will be using, aggregation tables arent used to do actual reporteing they are a backend lets say solution that speed up the information requestedon the fact table by the reports. or related to the dimensions itself as part of the table all depends on the approach
the only cons of aggregation tables lets say its they will growth the model size depending on the amount of data on it.
Proud to be a Super User!
Oh, I understand now. Thank you for this clarification!
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you, @MFelix. A good option to consider. StefanoGrimaldi mentioned this as well. However, we do 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 ,
There're 10 mehtods to speed up your report. I don't know if it can help you a bit.
1. Only keep necessary visuals
2. Use integers instead of text values whenever possible
3. Stick to Power BI’s default visuals
4. Avoid both-directional relationships in your data model
5. Understand the difference between duplicate and reference queries
6. Only load data which will be reported upon
7. Slicers can be real performance killers
8. Overthink your data modeling technique
9. Split granularity to lower cardinality
10. Measure performance: Get a clear view on how a report is currently performing so you can quantitively measure and compare the outcome of any of your taken actions. Power BI has a built-in feature, called the performance analyzer, enabling users to measure how much time is spent on rendering visuals and how much time is spent on executing DAX code. It even gives you the full extract of the DAX code, which you can further analyze in tools as DAX Studio.
About performance analyzer, you can refer to Use Performance Analyzer to examine report element performance.
And there're other Top 5 Power BI Performance Analyzer tools.
Also there's an official document you can refer to
Optimization guide for Power BI
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
139 | |
100 | |
95 | |
85 | |
63 |