DAX IF & Switch Statement Performance – Does Order Matter?

,

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:

Rate

Share

Share

Rate