skip to Main Content

Post by Rob Collie

SWITCH TRUE Alternative to Nested IF's

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:

image

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:

SWITCH TRUE Alternative to Nested IF's

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

SWITCH TRUE Alternative to Nested IF's

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. Smile

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 26 Comments
          1. Sorry for all the posts, but I just realized you already link to the article I put the url to in the comments above. That article also explains the use of SWITCH(TRUE (),…) like you have above. Still great stuff though – good reminder on it.

          2. Oh wow, I had never noticed that down near the end of Colin’s post! I’m not the most “aware” guy, and that’s putting it mildly. Something can be “brand new” to me without me realizing I’m the last person on the planet to see it 🙂

      1. This is great. I need an enhancement. I need a calculated column that references related column in another table
        ie in FACT table called SALES

        SWITCH(
        TRUE(),
        RELATED(CUSTOMER[FLAG_Y] = “Y” , 1 ,
        RELATED(VENDOR[FLAG_Y] = “Y” , 1,
        Sales[total] >10 , 1,
        0
        )

        is this possible??

  1. An IF construct can in most cases be replaced with Boolean Logic.
    (In fact SQL Server 2005 did not have an IF it only had a switch. IF came to SQL Server only in 2008)

    For Example in a Table Structured Reference in Excel or in a calculated Column in DAX

    a) IF([Sales]>1000,10%*A1,0) can be replaced with ([Sales]>1000)*10%*A1

    b) IF([Sales]>2000,20%*[Sales],IF([Sales]>1000,10%*[Sales],0)) can be replaced with
    ([Sales]>1000)*10% + ([Sales]>2000)*(20-10)%

    c) IF(AND([Sales]>1000,[Tenure]>5),10%*[Sales],0) can be replaced with
    ([Sales]>1000)*([Tenure]>5)*10%*[Sales]

    d) IF(OR([Sales]>1000,[Tenure]>5),10%*[Sales],0) can be replaced with
    ((([Sales]>1000)+([Tenure]>5))>0)*10%*[Sales]

    In DAX AND and OR are limited to testing 2 Conditions – If you need more than 2 conditions to be tested you need to resort to * or +

    For Text its slightly tricky

    e) IF([Sales]>1000,”Good”,”Not good”) can be replaced with
    REPT(“Good”,[Sales]>1000)&REPT(“Not Good”,[Sales]<=1000)
    In this case String concatenation which are generally very slow in Excel are likely to be slow in DAX calculated columns as well compared to an IF

    In Excel the only scenario that mandates the use of IF is when you want to return an a mixed array of Booleans (either TRUE or FALSE) and Numbers.

    1. In most of the DAX I’ve seen, and everything we write at my company, the idiomatic way of combining booleans is to use the && and || operators rather than the AND() and OR() functions. You can combine arbitrarily many of these, and they obviously respect parenthetical grouping.

      1. IS it true that the function AND() only works with 1 comparaative that is you cannot have A_expression AND B_expression AND C_expression yet you could use A_expression && B_expression && C-expression.
        ie && you can ‘nest’ whereas AND you cannot

  2. Rob, no worries if it’s old news on your site. It was new to me too and I appreciate the article. I was reading too fast and was baffled until I saw SWITCH([MyMeasure], was replaced with SWITCH(TRUE(), and then the light bulb went on!

  3. We use

    SWITCH( TRUE()
    ,HASONEVALUE( Dimension[HierarchyLevel] ), [LevelSpecificMeasure]
    ….
    )

    For all of our scoping statements in our DAX work at my company. Sometimes ISFILTERED() works better than HASONEVALUE(). The canonical use is for time intelligence, to show MTD at the month level, QTD at the quarter level, and YTD at the year level of a hierarchy:

    SWITCH( TRUE()
    ,HASONEVALUE( DimDate[Month] ), [MTDMeasure]
    ,HASONEVALUE( DimDate[Quarter] ), [QTDMeasure]
    ,HASONEVALUE( DimDate[Year] ), [YTDMeasure]
    ,[BaseMeasure]
    )

    This also includes an else argument, the [BaseMeasure] without a condition on the second to last line. This will display a value for Grand Total on any pivot (or if you roll up years to decades, for example), otherwise this value would be blank, as the default else argument is BLANK().

  4. Don’t ever forget Not(p) or q
    as in IF(p, IF(q, true, false), true)
    the formula engine likes that a lot.

    1. Yes it’s increasingly clear that I am late to the party 🙂

      On the plus side I’m thrilled every time I see your name appear on this site Jon. Love having you drop in 🙂

    1. You are quite welcome Ellen. I think it’s us Excel people who are surprised/delighted by this, while all the other backgrounds are like “meh, old news to us.”

  5. Hi
    Case:
    Dax Formula= SUMX(CALCULATETABLE(Order,Order[CUS_Id]=1000),Order[Price])*SWITCH([UOM_Id],’Sqm’,[Volume_Sqm],’pcs’,[Volume_pcs])

    Formula not working. Switch valid only for rows calculation?

  6. Ick! It is amazing that such a hack is required in any modern programming language. This makes the code nearly unreadable to any but the most adept at DAX. The fact that the order of operations is important is really bad. Why not simply implement a CASE statement similar to delphi/pascal that has been around for decades? None of these shenanigans are necessary with that elegant syntax. See here: http://www.delphibasics.co.uk/RTL.asp?Name=Case for an example…

  7. I’m really late to the party here ,but was quite stoked to find a way to use SWITCH(TRUE() to do a Contains & IS NOT NULL equivalent.

    Field = SWITCH(
    TRUE(),
    SEARCH(“A”, [column], 1, 0) > 0), “A”,
    SEARCH(“B”, [column], 1, 0) > 0), “B”,
    SEARCH(“C”, [column], 1, 0) > 0) && NOT(ISBLANK([column])), “C”, — Example to show how to add multiple conditions
    ,”D”
    )

  8. If the order of operations is a problem you can always use And() in addition. SWITCH(TRUE() , AND([Measure]>15 , [Measure]<=30) , "15 to 30" ….

Leave a Comment or Question