DAX – The Diabolical Genius of “SWITCH TRUE”

Post by Rob Collie

Did Someone Say Deliberately “Misuse” a DAX Function for Our Benefit?  We’re IN!

An End to Nested IF’s?  Sign Us Up!

When we first saw the SWITCH function make its debut in Power Pivot a few years back, it was a “hallelujah” moment.

Whereas we used to have to write nested IF’s, such as this:

   IF([MyMeasure]=1,expr1,
IF([MyMeasure]=2,expr2,
IF([MyMeasure]=3,expr3,…)))

Now , with SWITCH, we could write that much more cleanly as:

   SWITCH([MyMeasure],1,expr1,2,expr2,3,expr3…)

Which do you prefer?  It’s easy to make a strong “case for SWITCH,” isn’t it?

But What About Cases Other than Equals?

Now, let’s consider the following nested IF:

   IF([MyMeasure]<1,expr1,
IF([MyMeasure]<2,expr2,
IF([MyMeasure]<3,expr3,…)))

Notice that we’ve swapped out “=” for “<”.

And we can’t do that as a SWITCH, because SWITCH checks for exact matches between [Measure1] and 1 (or 2, 3, etc.)

This is unfortunate, because in these cases, we’ve had to keep using nested IF’s.  And wow do I (Rob) *hate* nested IF’s.  I can never seem to match the parentheses up correctly on the first try.

But There’s a Sneaky Antidote!  We CAN Still Use SWITCH!

Check THIS out.  Yep, this works:

   SWITCH(
TRUE(),
[MyMeasure]<1,expr1,
[MyMeasure]<2,expr2,
[MyMeasure]<3,expr3,
…)

I cannot take credit for this pattern.  I saw it somewhere else first, and I don’t even remember where.  As far as I know, this may be a pattern that’s been in use in SQL, MDX, and other programming languages for a long time.  I just had not seen it personally until within the last six months or so.

But I DO remember NOT understanding it, at all, the first time.  It confused the heck out of me actually.

Here’s Why it Works

SWITCH() is still testing for equivalence!  By providing the first argument as TRUE(), now each subsequent “test” is going to be checking for TRUE().

And since each of our inequality tests results in either TRUE() or FALSE() as a value, the test case that evaluate to TRUE() is the one that gets matched, and therefore the one that gets used.  For instance, if [Measure]<1 evaluates to TRUE(), then expr1 gets returned.

Brilliant, but there’s one nagging little stone in our mental shoe.

What About Overlap?  Does Order Matter?  Yes, it Does!

Consider this example pivot:

Now we add the following measure:

[Rank Group]:=

SWITCH(
TRUE(),
[Overall Store Rank]<10,”Under 10″,
[Overall Store Rank]<20,”Under 20″,
[Overall Store Rank]<30,”Under 30″,
“Over 30”
)

And we get this result, which is pretty cool:

[Rank Group] Measure Using SWITCH TRUE Instead of Nested IF’s

(Yes, measures can return text, which is WAY COOL, but not part of today’s lesson.  See here, here, and here for more.  They can also return dates, by the way!)

But the “weird” thing here is that Store 5, for example, “passes” all three of the tests in our SWITCH.  Yes, its rank (7) is less than 10.  But it’s also less than 20, and less than 30.  This understandably makes us a bit uneasy.

So let’s rearrange the formula a bit, and place the “less than 30” test first:

[Rank Group]:=

SWITCH(
TRUE(),
[Overall Store Rank]<30,”Under 30″,
[Overall Store Rank]<10,”Under 10″,
[Overall Store Rank]<20,”Under 20″,
“Over 30”
)

This yields:

Reordering the Test Cases in SWITCH Yields a Different Result
(The Under 10 and Under 20 Stores are Now Bucketed as Under 30)

Aha!  So the FIRST test case that matches, gets used.  Totally cool.  We can work with that for sure.

So, you STILL have to sequence your “overlapping” test cases carefully – just like you have to do with nested IF’s.  But hey, we get out of that crazy parenthesis-matching game, and get a more readable formula.

So make room on the SWITCH TRUE bandwagon.  We’re climbing aboard.