Intro

I’ve written a couple of article about IF & SWITCH in DAX over the past few years. Recently I noticed that there is a behavior I’ve never seen documented or described. Unfortunately, I cannot propose any workaround and I can see the behavior as kind of inconsistent. During one performance tuning session I noticed that there is different performance of the same measure when I change the order of branches in SWITCH statement.

Test Setup

Let’s try to make some simple demo to document this behavior. I have taken Adventure Works DWH (AW) demo solution and modified it slightly for my purpose. I reproduce the whole solution again in PowerBI Desktop, so you can download it and test it yourself. I would be happy to hear your input.

Let’s imagine you do not like having Independent measures as implemented in AW demo, but you would like to have something like measure dimension. To implement it, you might use Parameter Table Pattern. I’ve created a modified (slower) “Ugly” version of the measures for my test, to make the performance impact more significant.

This is the original Measure List:

This is my measure dimension:

New measures could be defined like this:

Now we have everything set for the test. I use direct measure definition in DAX Tests queries to be more flexible.

Test 1 – Does position of the option in SWITCH statement impacts performance?

In the first test we will compare performance of simple query showing “Total Margin” when “Total margin” is the first option of SWITCH and the other where “Total Margin” is the last option in SWITCH.

1^st query:

2^nd query:

As you can see, the only difference is the place where I put “Total Margin” in SWITCH command. I have also performed a test to put it into 3^rd position in SWITCH.

The following table summarizes the duration of the query on cold cache:

Test 2 – Is it SWITCH statement issue? Let’s try to rewrite it to IF statements.

I was wondering if I could maybe fix it by not using SWITCH and only using if statements. As I know SWITCH is just a syntax sugar. Now my DAX for the first option would look like this:

You can most likely imagine DAX for a respective version of calculation with “Total Margin” in the last place

Results:

Test 3 – MDX

I have the same solution as well in SSAS Tabular 2016 version, so I can test the impact as well in excel. This would be the excel pivot table report:

Test Results:

Test 4 – Measures vs Measure Dimension

The last test I did was just to show you the impact of the IF and SWITCH itself. So I’ll compare using measures directly versus using measure dimension in Excel Pivot Table.

This is how it looks in excel when we have measure dimension:

And here when we would use directly measures:

Test Results:

Summary of observations

Obviously the order of the options in switch command matters. I would like to summarize my observations not only from this test:

• We have seen counterintuitive behavior – better performance when being in last place of SWITCH.
• On the other hand, we have PROD solution where I observe the exact opposite behavior. Last place in SWITCH was less performant. Therefore, I would strongly recommend testing your case.
• This behavior is more obvious when using more dimensions in your query (like cross join)
• This behavior is more obvious when having some not trivial calculations in each branch.
• IF statement has the same behavior.
• Order of the options can generate different execution plan, especially with nontrivial calculations in branches. In our case (3k rows vs 4k rows)
• As you can see in the last test not using IF or SWITCH is of course the best option.

Here is the whole test for the curious of you who would like to see it with your own eyes.

The whole blog wouldn’t be possible without those great test tools:

• DAX Studio
• DAX Formatter
• Tabular Editor
• Snagit