Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors