Power BI Switch Function in DAX

BY: David Rohlfs

Introduction

There are so many variables when you build a DAX equation that it can become daunting to the untrained eye. The goal of this blog is to get you a little more prepared for a commonly use function, SWITCH (). The switch function is extremely useful for large VAR statements, to save time without using power query, or just a simple way to change a few rows of data.

In this blog we will go through a brief explanation of when to use the SWITCH () function, and a short walkthrough explaining how the SWITCH () function works.

 

When to use it

There are three main areas that you will see the SWITCH () function used in:

• Changing previously stated datapoints in an equation (Usually VAR Statements).
• Avoiding going into transform data to alternate a field of data.
• Simply switching a couple of words out for another word.

While there are many other situations to use the SWITCH () function in, these are just the main three and should not be counted as the complete list.

While you are still able to use a switch function on its own, what it is best at is when you are complementing a previous equation with a switch function. For instance, if you have an IF () statement, and you want to switch the TRUE with “final result”. Where a switch function really begins to shine is when you start using variables in your DAX equation. When you do this, you can have as many IF () statements in the VAR section of your equation, and you only need one SWITCH () function in your equation. You can see an example of this in EPM Strategy’s blog on Conditional Formatting with DAX.

When you use a SWITCH () function, you are generally taking a result that was given from another equation, or from the data, and turning it into something that is understandable for the end-user. This is where many uses for this function happen. As a report builder, the goal is to make a report understandable from an end-user perspective. And using a SWITCH () function allows for just that.

A couple of things to note when using the switch function:

• The switch function works for any data type.
• There is an optional else statement at the end of the function.
• You do need to state the field you want to use at the start of the function.
• You can use this function in either a measure or a calculated column. But you will most likely be using it inside of a calculated column.

 

Walkthrough

In this walkthrough we are going to look at an example from another blog on Conditional Formatting with DAX. In that blog we used a SWITCH () function as a RETURN statement and used multiple rules to determine which part of the equation would take place. We will also look at an example from docs.microsoft.com/en-us/dax/.

Before we go into the example, we are going to look at the syntax for the SWITCH () function.

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

We are taking the SWITCH () function and changing our <expression> with the result, depending on our value.

To demonstrate this, here is Microsoft’s example on a SWITCH () function.

= SWITCH([Month], 1, “January”, 2, “February”, 3, “March”, 4, “April”

, 5, “May”, 6, “June”, 7, “July”, 8, “August”

, 9, “September”, 10, “October”, 11, “November”, 12, “December”

, “Unknown month number”)

 

In this example, the report builder is switching a Month column’s data with the name of the month. Notice that they are using an “else” field as their last result. If you are not positive that your data is and always will be perfect, this field should always have a statement.

To show the depth of a switch function, here is an example from the Conditional Formatting with DAX blog.

 

We are switching the TRUE () expression with a color if the data meets the value criteria.

 

Conclusion

Although this function is simple to understand and learn, it can become one of the most crucial functions when you begin developing a deeper knowledge of DAX

Comments