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:
SelectedMeasure = IF ( HASONEVALUE ( MeasureName[MeasureType] ), VALUES ( MeasureName[MeasureType] ), “Total Sales” ) |
Amount := VAR CurrentMeasure = [SelectedMeasure] RETURN SWITCH ( CurrentMeasure, “Total Margin”, [Internet Total Margin Ugly], “Total Sales”, [Internet Total Sales Ugly], “Total Discount Amount”, [Internet Total Discount Amount Ugly], “Total Freight”, [Internet Total Freight Ugly], “Total Product Cost”, [Internet Total Product Cost Ugly], “Total Tax”, [Internet Total Tax Amt Ugly] ) |
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.
1st query:
DEFINE MEASURE ‘Internet Sales'[SwitchAmount] = VAR CurrentMeasure = [SelectedMeasure] RETURN SWITCH ( CurrentMeasure, “Total Margin”, [Internet Total Margin Ugly], “Total Sales”, [Internet Total Sales Ugly], “Total Discount Amount”, [Internet Total Discount Amount Ugly], “Total Freight”, [Internet Total Freight Ugly], “Total Product Cost”, [Internet Total Product Cost Ugly], “Total Tax”, [Internet Total Tax Amt Ugly] ) EVALUATE ( SUMMARIZECOLUMNS ( ‘Product Category'[Product Category Name], ‘Product'[Product Subcategory Name], “Amount”, CALCULATE( [SwitchAmount], MeasureName[MeasureType] = “Total Margin”) ) ) ORDER BY ‘Product Category'[Product Category Name], ‘Product'[Product Subcategory Name] |
2nd query:
DEFINE MEASURE ‘Internet Sales'[SwitchAmount] = VAR CurrentMeasure = [SelectedMeasure] RETURN SWITCH ( CurrentMeasure, “Total Sales”, [Internet Total Sales Ugly], “Total Discount Amount”, [Internet Total Discount Amount Ugly], “Total Freight”, [Internet Total Freight Ugly], “Total Product Cost”, [Internet Total Product Cost Ugly], “Total Tax”, [Internet Total Tax Amt Ugly], “Total Margin”, [Internet Total Margin Ugly] ) EVALUATE ( SUMMARIZECOLUMNS ( ‘Product Category'[Product Category Name], ‘Product'[Product Subcategory Name], “Amount”, CALCULATE( [SwitchAmount], MeasureName[MeasureType] = “Total Margin”) ) ) ORDER BY ‘Product Category'[Product Category Name], ‘Product'[Product Subcategory Name] |
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 3rd position in SWITCH.
The following table summarizes the duration of the query on cold cache:
Test Name | Query duration (ms) |
Test_1 SwitchTest Margin First |
2800 |
Test_1 SwitchTest Margin Third |
1600 |
Test_1 SwitchTest Margin Last |
1600 |
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:
DEFINE MEASURE ‘Internet Sales'[SwitchAmount] = VAR CurrentMeasure = [SelectedMeasure] RETURN IF ( CurrentMeasure = “Total Margin”, [Internet Total Margin Ugly], IF ( CurrentMeasure = “Total Sales”, [Internet Total Sales Ugly], IF ( CurrentMeasure = “Total Discount Amount”, [Internet Total Discount Amount Ugly], IF ( CurrentMeasure = “Total Freight”, [Internet Total Freight Ugly], IF ( CurrentMeasure = “Total Product Cost”, [Internet Total Product Cost Ugly], IF ( CurrentMeasure = “Total Tax”, [Internet Total Tax Amt Ugly], BLANK () ) ) ) ) ) ) EVALUATE ( SUMMARIZECOLUMNS ( ‘Product Category'[Product Category Name], ‘Product'[Product Subcategory Name], “Amount”, CALCULATE ( [SwitchAmount], MeasureName[MeasureType] = “Total Margin” ) ) ) ORDER BY ‘Product Category'[Product Category Name], ‘Product'[Product Subcategory Name] |
You can most likely imagine DAX for a respective version of calculation with “Total Margin” in the last place
Results:
Test Name | Query duration (ms) |
Test_2 IF Margin First |
2800 |
Test_2 IF Margin Last |
1500 |
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 Name | Query duration (ms) |
Test_3 MDX Switch First |
3900 |
Test_3 MDX Switch Last |
2400 |
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:
Test Name | Query duration (ms) |
Measure via Dimension |
13500 |
Measure via Measures |
7200 |
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:
8 Comments. Leave new
I have seen something similar sometime ago. As far as I remember I fixed the lackluster performance by making sure that the first IF branch was always BLANK(). In my case the scenario was way simpler than your example it was just a single IF( , ) so I ended up with this IF( , BLANK(), ). It’s some time ago so I don’t remember the exact details. In your case you could deMorgan the if conditions so the first branch will always return blank like this:
DEFINE
MEASURE ‘Internet Sales'[SwitchAmount] =
VAR CurrentMeasure = [SelectedMeasure]
RETURN
IF (
CurrentMeasure “Total Margin”
&& CurrentMeasure “Total Sales”
&& CurrentMeasure “Total Discount Amount”
&& CurrentMeasure “Total Freight”
&& CurrentMeasure “Total Product Cost”
&& CurrentMeasure “Total Tax”,
BLANK (),
IF (
CurrentMeasure = “Total Margin”,
[Internet Total Margin Ugly],
IF (
CurrentMeasure = “Total Sales”,
[Internet Total Sales Ugly],
IF (
CurrentMeasure = “Total Discount Amount”,
[Internet Total Discount Amount Ugly],
IF (
CurrentMeasure = “Total Freight”,
[Internet Total Freight Ugly],
IF (
CurrentMeasure = “Total Product Cost”,
[Internet Total Product Cost Ugly],
[Internet Total Tax Amt Ugly]
)
)
)
)
)
)
EVALUATE
(
SUMMARIZECOLUMNS (
‘Product Category'[Product Category Name],
‘Product'[Product Subcategory Name],
“Amount”, CALCULATE ( [SwitchAmount], MeasureName[MeasureType] = “Total Margin” )
)
)
ORDER BY
‘Product Category'[Product Category Name],
‘Product'[Product Subcategory Name]
If you do this and switch the measures around will the first real branch also take a performance hit? I’m just curious to know if this is the case. 🙂
Best regards.
Hi Oxenskiold,
Thanks for proposal. I can confirm it works for this case. So having first option as blank with nonsense condition. Results at similar performance of both queries. Query plan still differs by 1k lines but performance is now comparable. I’ll do another couple of test on my other real life case where impact of option order was vice versa.
Roman
You do know that there is a workaround for this right? The workaround probably won’t work in all scenarios but it has worked for me in at least one.
I’m plagued by poor IF and SWITCH performance…pray tell, what’s the workaround!?
Hi, As proposed by Oxenskiold making fist option empty with nonsense condition. Then other branches are delivering consistent performance.
Such as this example:
MEASURE ‘Internet Sales'[SwitchAmount] =
VAR CurrentMeasure = [SelectedMeasure]
RETURN
SWITCH (
CurrentMeasure,
“This is Workaround Option”, BLANK(),
“Total Margin”, [Internet Total Margin Ugly],
“Total Sales”, [Internet Total Sales Ugly],
“Total Discount Amount”, [Internet Total Discount Amount Ugly],
“Total Freight”, [Internet Total Freight Ugly],
“Total Product Cost”, [Internet Total Product Cost Ugly],
“Total Tax”, [Internet Total Tax Amt Ugly]
)
However it’s not solving in general poor performance of If but just this one particular case.
Roman
Hi, it just occurred to me – The Twelve-Coin Problem. A riddle how to find a counterfeit coin among 12 coins in just 3 balances on the scale. Not tested it but maybe analogous algorithm in SWITCH can improve performance.
Does order of measures is still issue in 2024?
Hello,
yes seems to be still case in some form. But of course you need to verify for your scenario. As well there is now possibility to use calculation groups for this scenario which might be worth to test.