Category: SWITCH

Excel Power Users are fans of the classic IF() statement. Powerful as it may be, it can get quite confusing when you nest them together. If you have never used the SWITCH() function in DAX, then you are missing out on an opportunity to simplify your conditional statements.

Complications of IF() Statements

Like you, I have used IF() for years. It has served me well and I have relied upon it for years. However, I always found it cumbersome when I needed complicated logic.

Let’s start with a basic list of vehicle models manufactured by Ford. If I wanted to quickly flag a particular model, I could do so with a basic IF() statement.

Ford F-150 Flag = 
IF ('Model List'[Model] = "F-150", "Yes", "No")

Where it gets complicated is when you nest your IF() statements. If I wanted to categorize the vehicles by type (truck, SUV, car, etc.), this would require a more complicated statement.

Vehicle Type IF = 
IF ('Model List'[Model] = "F-150", "Truck",
IF ('Model List'[Model] = "F-250", "Truck",
IF ('Model List'[Model] = "F-350", "Truck",
IF ('Model List'[Model] = "Ranger", "Truck",
IF ('Model List'[Model] = "Maverick", "Truck",
IF ('Model List'[Model] = "Mustang", "Car",
IF ('Model List'[Model] = "Explorer", "SUV",
IF ('Model List'[Model] = "Escape", "SUV",
IF ('Model List'[Model] = "Expedition", "SUV",
IF ('Model List'[Model] = "Edge", "SUV",
IF ('Model List'[Model] = "Bronco", "SUV",
IF ('Model List'[Model] = "Transit", "Van", "Error" 
))))))))))))

While effective, it becomes complicated. The DAX is bulky and requires you to maintain a number of parentheses to complete the formula. While harmless on its own, it could become cumbersome if you need to interject this into a larger formula.

Now, you might be reading this code and say “Hey Dom! There is a way to simplify this IF() statement! ” You are correct and I will review those methods shortly but let’s keep the statement simple for now.

SWITCH() It Up and Clean It Up

I can take that basic IF() statement and simplify it using the SWITCH() function. This function allows you to simplify the logic so it is easier to manage. The basic syntax of the function is:

SWITCH(<expression>, <value>, <response> [, <value>, <response>, … ] [, <else>])

Simply put, this function will evaluate an expression and compare it to a list of values and provide the appropriate response. It also contains an else clause which fills in any other gaps you might have. If we apply it to our complicated IF() statement, we can simplify it to this:

Vehicle Type SWITCH =
SWITCH ('Model List'[Model],
    "F-150", "Truck",
    "F-250", "Truck",
    "F-350", "Truck",
    "Ranger", "Truck",
    "Maverick", "Truck",
    "Mustang", "Car",
    "Explorer", "SUV",
    "Escape", "SUV",
    "Expedition", "SUV",
    "Edge", "SUV",
    "Bronco", "SUV",
    "Transit", "Van",
    "Error"
)

Now that is much easier to read and understand. The SWITCH() function will evaluate which model is selected and then check it against a list of values. If the model is found in the list, then it will bring back a response. If the model is missing, then it will bring back “Error” as specified in the else statement.

But the really cool part about the SWITCH() function is that we can flip it upside down. Instead of specifying an expression and checking it against a value, we can specify a value and check it against a list of expressions. Let’s say we want to bucket the price of the F-150 line of trucks. Since there are different trim packages, the range is quite considerable. We can use SWITCH() to accomplish this goal.

Price Buckets = 
SWITCH (TRUE (),
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

In this expression, the value we are looking for is TRUE(). From there, our list of expressions will come back either TRUE() or FALSE(). I can still accomplish the same with an IF() statement, but this is so much cleaner.

Price Buckets =
IF ('Model List'[Price] <= 36000, "Low",
    IF ('Model List'[Price] <= 55000, "Mid",
        IF ('Model List'[Price] > 55000, "High"
        )
    )
)

Both methods work well, but the SWITCH() function keeps the code clean. This will be important as we start to build more complicated measures.

SWITCH()-ing Directions

So far we have only been working with simple logic statements. However, sometimes these logic statements become far more complicated. In our list of F-150s, there are two models which are classified as emergency vehicles. They are not to be sold to the general public. We also have a sport and electric trucks that are not part of our standard report. As a result, we want to clean up our reporting and remove them from our pricing buckets. Let’s try a few different ways to manage this.

Option 1 – Add Model Types to the SWITCH() Statement

The easiest option to start is to add the model types to the SWITCH() statement we used above. This will require us to add three values to our SWITCH statement to clean up the list.

Price Buckets v1 = 
SWITCH (TRUE (),
    'Model List'[Model] = "Emergency", "N/A",
    'Model List'[Model] = "Electric", "N/A",
    'Model List'[Model] = "Sport", "N/A",
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

There is nothing wrong with this method as it works well, but we can simplify those three lines into a single line.

Option 2 – AND/OR Modifiers

In the first section I mentioned there was a way to simplify our IF() statements. Excel users are very familiar with using AND()/OR() statements to handle multiple logic arguments at once. The only catch is that these functions can handle two arguments at once. Since we have three vehicle types to consider, we will need to chain two items together.

Price Buckets v2 = 
SWITCH (TRUE (),
    OR ('Model List'[Model] = "Emergency" OR ('Model List'[Model] = "Electric", 'Model List'[Model] = "Sport")), "N/A",
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

This method works, but like the IF() statements, they get complicated very quickly. This is not clean and hard to maintain if you want to modify the list of model types in the future.

Option 3 – Using AND/OR Without Functions

A hidden secret with conditional statements is how to use AND/OR without the function. We can substitute AND() with “&&” to chain as many statements together as we want. Also, we can substitute OR() with “||” and accomplish the same thing. This eliminates the need to manage the parentheses associated with the functions

Price Buckets v3 = 
SWITCH (TRUE (),
    'Model List'[Model] = "Emergency" || 'Model List'[Model] = "Electric" || 'Model List'[Model] = "Sport", "N/A",
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

This method still works well, but it is still not the cleanest way to keep everything together.

Option 4 – Combine SWITCH() Statements

Since we have a specific list of vehicle types that should not be bucketed, we can use one statement to handle which vehicle types should not be bucketed while another assigns buckets to vehicles that can be bucketed.

Price Buckets v4 = 
SWITCH (TRUE (),
    ( 'Model List'[Model] ) IN { "Emergency", "Electric", "Sport" }, "N/A",
    SWITCH (TRUE (),
        'Model List'[Price] <= 36000, "Low",
        'Model List'[Price] <= 55000, "Mid",
        'Model List'[Price] > 55000, "High"
    )
)

In this sample code, we have used two SWITCH() functions. The first function simply checks the vehicle model information. I even cleaned up the expression to evaluate the vehicle model similar to a IN expression found with SQL statements.

I then took a second function that came through in the else statement to evaluate the price bucket for models not already dispositioned in the first SWITCH() function.

Thinking About SWITCH()-ing?

Now, I will admit that I am complicating the formula in that last option. But I wanted to show how easy it can be to inject a SWITCH() function inside of an existing function. It took me a while to get used to using SWITCH(). In fact, before writing this article, I still used IF() because I did not think I could chain AND()/OR() functions. After trying it out for this article, I even learned that I can probably eliminate IF() from most of my DAX formulas.

Are you using SWITCH() at all with your DAX formulas? Do you think it is easier than IF()? Tell me in the comments below!