SWITCH
Power BI · Advanced DAX & Time Intelligence
Theory
SWITCH(expression, value1, result1, value2, result2, ..., else) evaluates an expression and returns the result for the first matching value, or the optional else result if nothing matches. It replaces long chains of nested IF statements and is far easier to read. A simple use maps codes to labels, for example SWITCH(SELECTEDVALUE(Sales[Region]), "North", "N", "West", "W", "Other").
The most popular pattern is SWITCH(TRUE(), condition1, result1, condition2, result2, ..., else): because the expression is TRUE, the first condition that is true wins, so you can test ranges. A Customer Tier measure with [Total Sales] >= 2500 → "Gold", >= 1000 → "Silver", else "Bronze" gives Asha Gold (₹2,700), Ravi Silver (₹2,400), and Meera (₹600) and Karan (₹500) Bronze. Conditions are checked in order, so put the strictest first. SWITCH is also used for dynamic measure selection and conditional-format colours.
Why we use it
Business rules often have several branches: tiers, bands, status labels. SWITCH expresses them clearly and keeps the logic in one maintainable place.
When to use it
Use SWITCH whenever you have more than two branches. Use IF for a single condition. When the bands must be used on slicers or axes, put the same logic in a calculated column or Power Query instead of a measure.
How it works
- Evaluate Customer Tier for a customer
- Is Total Sales >= 2500? Yes: Return Gold. No: Check the next condition.
- Is Total Sales >= 1000? Yes: Return Silver. No: Return the else value Bronze.
- Tier shown in the visual
Syntax
SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
SWITCH(TRUE(), <condition1>, <result1>, <condition2>, <result2>, <else>)Example
Customer Tier =
SWITCH(
TRUE(),
[Total Sales] >= 2500, "Gold",
[Total Sales] >= 1000, "Silver",
"Bronze"
)
Region Code = SWITCH(SELECTEDVALUE(Sales[Region]), "North", "N", "West", "W", "South", "S", "All")Key points
- Returns the result of the first matching value
- SWITCH(TRUE(), ...) tests conditions in order
- Put the strictest condition first
- The last argument without a pair is the else result
- A measure result cannot be used as a slicer; use a column for that