How to Use SWITCH in DAX in Power BI: Simple Guide

Use the SWITCH function in DAX to evaluate an expression against multiple values and return a result for the first match. It works like a cleaner alternative to nested IF statements, making your formulas easier to read and maintain.

Syntax

The SWITCH function syntax is:

• SWITCH(expression, value1, result1, value2, result2, ..., else_result)

Explanation:

• expression: The value or expression to compare.
• value1, value2, ...: Possible values to match against the expression.
• result1, result2, ...: Results returned when the expression matches the corresponding value.
• else_result (optional): Result if no match is found.

DAX

SWITCH(
    expression,
    value1, result1,
    value2, result2,
    ...,
    else_result
)

Example

This example shows how to categorize sales amounts into labels using SWITCH. It checks the SalesAmount and returns a category:

DAX

Sales Category =
SWITCH(
    TRUE(),
    Sales[SalesAmount] < 1000, "Low",
    Sales[SalesAmount] < 5000, "Medium",
    Sales[SalesAmount] >= 5000, "High",
    "Unknown"
)

Output

For SalesAmount = 750, output: "Low"
For SalesAmount = 3000, output: "Medium"
For SalesAmount = 7000, output: "High"

Common Pitfalls

Common mistakes when using SWITCH include:

• Not using TRUE() as the expression when testing multiple conditions, which causes unexpected results.
• Forgetting the else_result, which returns BLANK() if no match is found.
• Using complex expressions directly in value positions instead of in the main expression.

Correct use with TRUE() allows conditions like in IF statements.

DAX

/* Wrong way: expression is a column but values are conditions */
SWITCH(
    Sales[SalesAmount],
    Sales[SalesAmount] < 1000, "Low",  /* This won't work as expected */
    Sales[SalesAmount] < 5000, "Medium",
    "High"
)

/* Right way: use TRUE() and conditions as values */
SWITCH(
    TRUE(),
    Sales[SalesAmount] < 1000, "Low",
    Sales[SalesAmount] < 5000, "Medium",
    "High"
)

Quick Reference

Part	Description
expression	Value or expression to evaluate once
value1, value2, ...	Values to compare against expression
result1, result2, ...	Results returned for matching values
else_result (optional)	Result if no match found, defaults to BLANK()

Key Takeaways

Use SWITCH to simplify multiple condition checks instead of nested IFs.

Use TRUE() as the expression to evaluate logical conditions in SWITCH.

Always provide an else_result to handle unmatched cases.

SWITCH improves formula readability and maintenance in Power BI.

Avoid placing complex conditions in value positions; use TRUE() for conditions.