When building measures and calculated columns in Power BI, we often face situations where we need to evaluate multiple possible outcomes and return different results based on conditions. While IF statements can handle this, they can become cumbersome, confusing, and hard to read when there are many branches.
The SWITCH function in DAX is a cleaner, more efficient alternative for handling multiple condition checks. Let’s take a look.
SWITCH Function Signature
The basic DAX syntax of the SWITCH function is:
SWITCH(<expression>,
<value1>, <result1>,
<value2>, <result2>,
...,
[<else>])
<expression> – The value or expression you want to evaluate once.<valueN> – A possible value that the expression could equal.<resultN> – The result to return if the expression equals the corresponding <valueN>.[<else>] – (Optional) The default result if none of the value-result pairs match.
Note: the <expression> is evaluated once and compared against multiple <value> options in sequence until a match is found.
Example 1 – Basic SWITCH Usage
Suppose we have a Customer[Category] column containing numeric codes:
- 1 = “Bronze”
- 2 = “Silver”
- 3 = “Gold”
We can translate these codes into readable labels using SWITCH as in the following DAX code example:
Customer Category Label =
SWITCH(Customer[Category],
1, "Bronze",
2, "Silver",
3, "Gold",
"Unknown"
)
Explanation:
– The Customer[Category] column is evaluated once.
– If it equals 1, "Bronze" is returned; if 2, "Silver"; if 3, "Gold".
– Otherwise, "Unknown" is returned.
Example 2 – Multiple Match Checks
If we wanted to calculate a commission rate based on a sales tier:
Commission Rate =
SWITCH(Sales[SalesTier],
"Low", 0.02,
"Medium", 0.05,
"High", 0.08,
0
)
Explanation:
– The Sales[SalesTier] column is evaluated once.
– If it equals "Low", 0.02 is returned; if "Medium", 0.05; if "High", 0.08.
– Otherwise, 0 is returned.
The SWITCH TRUE Pattern
This is a really cool and handy usage of the function. Sometimes, we don’t have a single value to compare against. Instead, we want to evaluate different logical conditions. In these cases, we can use the SWITCH TRUE pattern, which works like multiple IF statements but is much cleaner.
DAX Syntax:
SWITCH( TRUE(),
<condition1>, <result1>,
<condition2>, <result2>,
...,
[<else>])
Here, TRUE() acts as the <expression>. Each <condition> is a Boolean expression that returns TRUE or FALSE. The function returns the corresponding result of the first condition that evaluates to TRUE.
Example 3 – SWITCH TRUE for Ranges
Suppose we want to grade students based on their score:
Grade =
SWITCH(TRUE(),
Scores[Score] >= 90, "A",
Scores[Score] >= 80, "B",
Scores[Score] >= 70, "C",
Scores[Score] >= 60, "D",
"F"
)
Explanation:
– Each Scores[Score] comparison statement is evaluated in order from top to bottom, and returns the first match.
– If Scores[Score] >= 90, “A” is returned; if Scores[Score] >= 80, “B”; if Scores[Score] >= 70, “C”, if Scores[Score] >= 60, “D”
– Otherwise, “F” is returned.
Note: Other more complex conditions, such as ones using OR and AND logic or including complex calculations, can be used.
Why SWITCH is such a great, clean, easy to use function:
- No nested IFs.
- Each condition is independent.
- Easy to add or modify conditions.
When to Use SWITCH Instead of IF
While IF can achieve the same results, SWITCH has several advantages:
- Readability –
SWITCH structures conditions in a clear, top-to-bottom list.
- Maintainability – Easier to add, remove, or change cases without dealing with messy nested parentheses.
- Performance – In some cases,
SWITCH can be more efficient because the expression (in the basic form) is evaluated once, not multiple times as with nested IF statements.
- Logical Branching – The
SWITCH TRUE pattern handles complex conditions without deep nesting.
Example 4 – IF vs. SWITCH
Let’s take a look at a comparison example:
IF Version:
Category Label =
IF(Customer[Category] = 1, "Bronze",
IF(Customer[Category] = 2, "Silver",
IF(Customer[Category] = 3, "Gold", "Unknown")
)
)
SWITCH Version:
Category Label =
SWITCH(Customer[Category],
1, "Bronze",
2, "Silver",
3, "Gold",
"Unknown"
)
Result: As you can see, the SWITCH version is shorter, easier to read, less error-prone, and easier to maintain.
Key Takeaways
- Use basic SWITCH when comparing one expression to multiple possible values.
- Use SWITCH TRUE when checking multiple conditions or ranges.
- SWITCH often results in cleaner, more maintainable DAX than deeply nested IF statements.
- Always include a default (else) value to handle unexpected cases.
Thanks for reading and I hope you found this useful.
The Data Community 