The SWITCH function in DAX can be used to replace the nested IF statement.  It is both easier to read and easier to write. SWITCH was introduced in PowerBI in Nov 2016 and it is also available in updated versions of Excel 2016

By the end of this article and examples, you will

• Understand the syntax for SWITCH
• Know how you can solve simple a problem using a nested IF statement
• Learn how you can replace a nested IF statement with SWITCH
• Know when to combine SWITCH with TRUE
• Learn how to group data into classification buckets using SWITCH with real case business problem

This article contains a data table to download so you can practice along and master the art of using SWITCH expression in DAX.  We are also powered with blockchain so you can earn while you learn.  

Understanding the SWITCH syntax

The syntax for SWITCH is:

SWITCH ( , , [, , [, … ] ] [, ] )

Where:

Expression: any DAX expression to be evaluated that returns a single scalar value (number, string, or date), where the expression is to be evaluated multiple times (for each row/context).

Value: if the expression has this value the Result will be returned

Result: assuming the Value is returned by the expression, this is the value that will be returned

Else:  If the expression does not return the Value, Else is returned.

Both the Value and Results can be repeated, just like creating a nested IF statement.

SWITCH will look for an exact match where the Expression equals the value and will return the result.

Example use of SWITCH expression in DAX

A company provides a benefits package which includes a financial bonus depending on the role of the employee and bonus days paid leave depending on the number of days absent.  The criteria for the benefits are laid out in the table below.

Given the table employee_details, we want to display the % bonus for each employee as a calculated column.  We also wish to display the number of bonus days leave each employee will be awarded based on their absentees.

Solving % Bonus with an IF statement

The syntax for an IF statement is

=IF(Logical Test, Value if True, Value if False)

To add the % bonus for each employee based on the employee role we would use the following formula

=IF(employee_details[Role]=”Manager”, 0.15, IF(employee_details[Role]=”Sales Rep”, 0.25, IF(employee_details[Role]=”Accountant”,0.1)))

Where

IF(employee_details[Role]=”Manager”, would be the first logical test and the Value if True is 0.15

And

IF(employee_details[Role]=”Manager”, would be the second logical test and the Value if True is 0.25

And

IF(employee_details[Role]=”Accountant”, would be the third logical test with a Value if True being 0.1

There is no value if false in this example.

Solving % Bonus using SWITCH function in DAX

The syntax for SWITCH is

SWITCH ( , , [, , [, … ] ] [, ] )

In this case our Expression is the column that contains the employee Role.  Our first value would be “Manager” and the Value, if it is Manager would be 0.15.  Our second Value would be Sales Rep and the Value, if it is Sales Rep, would be 0.25. The third Value would be Accountant and the value given if the employee is an Accountant would be 0.1

=SWITCH(employee_details[Role],”Manager”,0.15,”Sales Rep”,0.25,”Accountant”,0.1)

As you can see, the SWITCH expression in DAX is a lot easier to read, and to write, over the nested IF statement.

When to combine SWITCH with TRUE

As SWITCH will look for an exact match where the Expression equals the value and will return the result, we have a problem if we wish to look for a non exact match, such as greater than or less than.

To overcome this we can use TRUE() as the Expression to be evaluated.

Solving bonus days leave with SWITCH

Now that you understand when to combine SWITCH with TRUE we can move forward and add the bonus days leave as a calculated column to our table.

Our SWITCH formula would begin as follows

=SWITCH(TRUE(),

After this we can then enter our logical statement as our value.  Our first value would be if the missed days are less than 2

=SWITCH(TRUE(),employee_details[Missed Days]<2

Next this we would enter the result given that the missed days is less than 2, which in this case is 3 bonus days.

=SWITCH(TRUE(),employee_details[Missed Days]<2,3

We can then add our additional logical statements as values.  These are the value if the employess missed less than or equal to 5 days and less than or equal to 7 days

=SWITCH(TRUE(),employee_details[Missed Days]<2,3,employee_details[Missed Days]<=5,2,employee_details[Missed Days]<=7,1

Finally, we have the Else.  If none of the Values given are met, we can enter and Else value.  This can be compared to value if False in an IF statement.  As this option is in square brackets [] we have the option to leave it blank. 

=SWITCH(TRUE(),employee_details[Missed Days]<2,3,employee_details[Missed Days]<=5,2,employee_details[Missed Days]<=7,1)

It is important to note that the order in which you place the criteria are important.  The SWITCH function will evaluate each Value until it finds the first true, at which point it will then stop.  Therefore, if we were to write

=SWITCH(TRUE(),employee_details[Missed Days]<=7,1,employee_details[Missed Days]<=5,2,employee_details[Missed Days]<2,3)

On evaluation of the first employee Mary, it will see that the missed days is 5, which is less than or equal to 7 and find that to be true and so return only 1-day bonus. The SWITCH function, as it has found a true on the first test, will not continue to test the other VALUES in the statement. In this case, the returned value is not correct.

When the calculation moves to the second employee for evaluation, Amber, it will see 0 missed days. This is less than 7 and therefore will return only 1 bonus day.  The SWITCH expression, as it has found a true on the first test, will not continue to test the other VALUES in the statement.  In this case the returned value is not correct.

As you can see, it is important to think logically when writing a SWITCH expression combined with TRUE() to ensure you get the results you expect.  If you place the values in the wrong order, you will get the wrong results.

Learn and Earn Activity – Advanced DAX SWITCH for you to practice

This activity will give you the skills to use SWITCH to classify data into different buckets.  This is your chance to earn some tokens while you learn.

We have been given the following table of data which includes all outstanding invoices on a ledger. 

Your task is to create a visualization showing the total outstanding balances based on the age of the invoices within the following classification brackets:

0-15 days old

16-30 days old

31-60 days old

61+ days old

You can select any visualization you deem appropriate to best display the data.   This is the chart we prepared in Excel

To earn rewards on this post, in the comment section below, answer the following questions

• What is the DAX expression you used to classify the age of the invoice?
• Why did you create a calculated column over a measure?
• Share a copy of the visualization you created

Tip: DATEDIFF expression in DAX

The DAX expression DATEDIFF can be used in this example.  DATEDIFF will get the difference between 2 dates (assuming the data type DATE or DATE/TIME or TIME) expressed as the selected interval.  The interval can be Day, Month, Quarter, Week, year, or it could be Hour, Minute or Second

The syntax for DATEDIFF is

=DATEDIFF(Date 1, Date 2, Interval)

For Example, we wish to calculate the number of days between two dates as per the table below

We would use the expression

=DATEDIFF(invoiceage[First Date],invoiceage[Second Date],DAY)

Next week I will provide a video solution to the earn and learn activity, so get practicing and posts your solutions so you can earn while you learn.