SWITCH vs IF (increase performance?)

[fjjpeeters1976]

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 =

VAR alloc = [BAE_BLS Allocation]

VAR swit =

    SWITCH (

        alloc,

        "1", [Total Split - BAE_CEN All],

        "2", [Total Split - BAE_BLS All],

        "3", [Total Split - BAE_BLA ALL],

        "4", [Total Split - SE12A All],

        "5", [Total Split - SE14A ALL],

        "6", [Total Split - BAF All],

        "7", [Total Split - BAA ALL],

        "8", [CTO Split - BAE_BLS ALL],

        "9", [CTO Split - BAE_BLA All],

        "10", [CTO Split - BAF All],

        "11", [CTO Split - BAA All],

        "12", [Europe Split - BAE_CEN All],

        "13", [Europe Split - BAE_BLS all],

        "14", [Europe Split - BAE_BLA all],

        "15", [Europe Split - SE12A All],

        "16", [Europe Split - SE14A all],

        "17", [Finland Split - BAE_CEN All],

        "18", [Finland Split - BAE_BLA all],

        "19", [Finland Split - BAE_BLS all],

        "20", [Finland Split - BAF ALL],

        "21", 0.0,

        "22", 0.25,

        "23", 0.375,

        "24", 0.5,

        "25", 0.75,

        "26", 0.9,

        "27", 1.00

    )

RETURN

    swit

[v-tejrama · Accepted solution]

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.

[Poojara_D12]

HI @fjjpeeters1976 

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.

[v-tejrama]

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 Support

[v-tejrama · Accepted solution]

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.

[fjjpeeters1976 · Accepted solution]

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.

[MasonMA]

Hi @fjjpeeters1976 

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:)