[DAX] SWITCH

Are you getting tired of writing “if” functions? More specific, are you getting tired of writing a lot of “if” functions with different criteria? If yes, here comes an easy-to-understand and easy-to-debug method: SWITCH.

SWITCH = Nested If Statements

Syntax

SWITCH(expression,
value1, result1,
value2, result2,
…
…
else
)

• expression is any DAX expression that returns a single scalar value (number, string, or date), where the expression is to be evaluated multiple times (for each row/context).
• value1 is a constant value to be matched with the evaluated result of expression.
• result1 is any scalar expression (i.e. one that returns a scalar value) to be evaluated if the results of expression match the corresponding value1.
• else is any scalar expression to be evaluated if the result of expression doesn’t match any of the value arguments
• expression, value1, and result1 are the only mandatory parameters in SWITCH.

Example 1.

YearHalf =
SWTICH (
‘Calendar’[Month],
1, “1H”,
2, “1H”,
3, “1H”,
4, “1H”,
5, “1H”,
6, “1H”,
7, “2H”,
8, “2H”,
9, “2H”,
10, “2H”,
11, “2H”,
12, “2H”,
“-”
)

Example 2. SWITCH() & TRUE()
It’s common to see SWITCH and TRUE being used together in the DAX code. With the TRUE() function, you don’t need to worry about the “else” expression in the SWITCH syntax.

Pricing =
SWITCH(
TRUE(),
‘Product’[Price] = 0, BLANK(),
‘Product’[Price] < 8000, “Low Pricing”
‘Product’[Price] < 25000, “Medium Pricing”
‘Product’[Price] < 50000, “High Pricing”
)

Example 3. SWITCH() & SEARCH()
Assuming you would like to flag a sentence as COVID-related if there are any words in the sentence such as “covid”, “corona”, “virus”, “pandemic”. You can use “SEARCH” function along with SWITCH to flag the sentence.

COVID-Related =
SWITCH(
TRUE(),
SEARCH(“covid”, Comment [Review],,0)>0,”True”,
SEARCH(“pandemic”, Comment [Review],,0)>0,”True”,
SEARCH(“corona”, Comment [Review],,0)>0,”True”,
SEARCH(“virus”, Comment [Review],,0)>0,”True”
)

Conclusion

We can find that SWITCH is easier to understand than nested if statements. Here are some pros of SWITCH.

• Easy to write, understand, and read (especially compared with nested if)
• Easy to debug when there is an error message