Slow switch measure

[D0minique]

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://social.msdn.microsoft.com/Forums/windowsapps/en-US/92acbe55-242f-4578-99b1-fc9464e425ea/dax-...

- https://stackoverflow.com/questions/61805513/force-dax-switch-function-to-use-strict-lazy-short-circ...

- 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.

[MFelix]

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/

[D0minique]

Hi @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....

[StefanoGrimaldi]

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 

[D0minique]

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)], etc.
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] ) ) https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expre...	No improvement

[StefanoGrimaldi]

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. 

[D0minique]

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...

[StefanoGrimaldi]

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. 

[D0minique]

Oh, I understand now. Thank you for this clarification!

[MFelix]

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

[D0minique]

Thank 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...

[v-stephen-msft]

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.

[D0minique]

Hi @v-stephen-msft, thank you for this nice overview!