Forum Discussion
SWITCH vs IF (increase performance?)
I have the following SWITCH calculation and for items 1-20 there is quite a heavy calculation logic behind it. When implementing this statement the refresh became extremely slow. Chat GPT is saying that a nested IF statement might be the better option here. Would this increase performance?
BAE_BLS Allocation switch =
Hi fjjpeeters1976 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
hi, thanks very much for your help. It made it indeed faster. Still it is rather slow but I think that is more due to the size of the report and the complicated formulars behind it. I will focus more on those now.
5 Replies
Well switching to nested IF will make your code unreadable for sure with more than 3 nested conditions:). I think your code looks clean and neat with SWITCH!
In terms of performance, i'm thinking it's more related to the Measures inside your SWITCH if they are CALCULATE with heavy filters or complex iterators like SUMX, FILTER.
Hope this helps:)
Hi fjjpeeters1976 ,
Thanks for reaching out to the Microsoft fabric community forum.I used a regular SWITCH statement in my DAX measure to handle values from 1 to 27. For values 1 to 20, I called some complex measures (using SUMX, FILTER, etc.), and for 21 to 27, I returned simple constants like 0.25, 0.5, and 1.
However, the report visuals became very slow, especially when filters were applied or pages were changed. Switching to nested IF statements made the code harder to read and didn’t help with performance.
I then tried using SWITCH(TRUE(), ...) instead of SWITCH(column, ...), updating my logic so that each condition checked if alloc equaled a certain value. For example, alloc = 1 would return [Heavy Measure 1], and alloc = 22 would return 0.25, and so on.
This change significantly improved performance, with visuals loading much faster. The reason seems to be that the regular SWITCH might evaluate all branches, especially when returning measures, while SWITCH(TRUE()) stops at the first match, reducing unnecessary calculations.
After testing both approaches, the results were the same, but SWITCH(TRUE()) was clearly faster.
Please find the attached PBIX and Screenshort file for your reference.
Best Regards,
Tejaswi.
Community SupportHi fjjpeeters1976 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
hi, thanks very much for your help. It made it indeed faster. Still it is rather slow but I think that is more due to the size of the report and the complicated formulars behind it. I will focus more on those now.
Replacing your SWITCH with nested IF is unlikely to improve performance significantly. The slow refresh is due to the complexity of the calculations inside each case, not the control structure used.
To improve speed, focus on optimizing the heavy calculations themselves, reducing their evaluation frequency, or restructuring the logic to pre-calculate or simplify the expressions.
