cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
selimovd
Super User
Super User

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

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.

 

measures.png

 


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

1 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

View solution in original post

6 REPLIES 6

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

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!

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:

ValtteriN_0-1660735365717.png

 

ValtteriN_1-1660735383766.png

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/

 







Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




selimovd
Super User
Super User

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

johnt75
Super User
Super User

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

Thank you for the proposal @johnt75.

I will give it a try. 

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.