Performance with many SWITCH or IFs slow. Is there a solution with good performance?

[selimovd]

Hello everyone,

I have a kind of complex scenario and don't really know how I can solve that with appropriate performance.

I have a master measure that is, depending on a slicer, calculating a different output. With a "basic" selection it's calculating the sum of the value column, if the user is chosing a "corrected" value in the slicer it's adding the corrected values and if the user is chosing "normed" then it's adding the normed values to the basic values.
This works perfectly.

This master measure is the base measure for all of my other calculations. For example my [Sales] measure or [Sales PY] or [Budget] or [Budget PY]. Also this works perfectly.

Every measure has a good performance on its own (max 250ms, usually around 15-150 ms)

My problems start now at the last stage.

My customer wants 15 different comparisons for each measurement in one measure. He wants to select the comparison in a slicer, for example "Actual vs. Budget" or "Actual vs. Forecast 1", "Actual vs. Actual PY", "Budget vs. Budget PY", etc.
Then the measure should calculate the correct value.

The approach was to calculate that with a SWITCH, what in general also works. But it's really slow as SWITCH seems to calculate all 15 comparisons.

The problem is that the customer wants to show multiple of these comparisons in one visual. For example one for sales amount, one for number products sold, one for contribution margin 1, one for contribution margin 2, etc.
Each of these measures seems to calculate all 15 scenarios with the switch and at the end this is really adding up and the visuals needs about a minute to calculate the right values for all measurements and all scenarios.

I tried to solve that with field parameters. Each of the 15 cases for each measure is a single measure and in general it works. The performance is now a few seconds, what is acceptable.

Now for the ZebraBI waterfall chart needs a columns and a measure that is taking the correct measure. So I would need again a SWITCH to figure out the correct measure to be taken. Basially that means it makes the field parameters useless.

Does someone have a solution for that?

Thank you and best regards
Denis

[marcorusso · Accepted solution]

Calculation groups may not help, the best performance can be obtained by using SWITCH but you have to pay attention to the details. See this article+video: Understanding the optimization of SWITCH - SQLBI

[selimovd]

Thank you very much @marcorusso , the approach mentioned in the article could solve the issue.

The visual with 20 measures that all of them use a SWITCH with 19 options went from 40 seconds to 1.8 seconds after optimization!

[ValtteriN]

Hi @selimovd @marcorusso,

Just proposing an alternative here that has worked pretty well in my models: 

You couls use a field parameter to dynamically calculate the measure you want. This way we can skip the optimization process of SWITCH:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[selimovd]

What would you consider the most promising approach @marcorusso and @AlbertoFerrari? 

[johnt75]

You could look into calculation groups. They can be nested by using the precedence setting and should only calculate the measures that you need rather than all of them

[selimovd]

Thank you for the proposal @johnt75.

I will give it a try.