SWITCH
Compares an expression with a list of values and returns the result for the first match.
SWITCH(<expression>, <value1>, <result1> [, <value2>, <result2>] ... [, <else>])Returns: The matching result (a scalar). The else value, or BLANK, when nothing matches.
What it does
SWITCH checks one expression against a list of values, in order, and returns the result attached to the first match. It replaces a chain of nested IF statements with one clear list. Its most powerful use is a scenario selector: a slicer picks Actual, Budget or Forecast, and one measure returns the right figure. Put the most likely cases first, because the first match wins.
Syntax and arguments
- expression: the value to test, such as a text slicer value, or TRUE() when you test a list of conditions.
- value1, result1, value2, result2 ...: pairs of a value to match and the result to return when it matches.
- else (optional): the result when nothing matches. If left out, the result is BLANK.
Example
The finance team builds receivables aging buckets. Each invoice is placed in a bucket by the number of days past due, and a single measure returns the amount in that bucket.
Aging Bucket =
SWITCH(
TRUE(),
Invoices[DaysPastDue] <= 0, "Current",
Invoices[DaysPastDue] <= 30, "1 to 30",
Invoices[DaysPastDue] <= 60, "31 to 60",
Invoices[DaysPastDue] <= 90, "61 to 90",
"Over 90"
)
Using TRUE() as the expression lets each row test its own condition. The first true condition wins, so the order matters. The final text is the else value, used for everything over 90 days.
Example: a second, different use
A scenario selector that switches a measure between actual and budget, driven by a disconnected table with a slicer:
Selected Amount =
SWITCH(
SELECTEDVALUE(Scenario[Scenario]),
"Actual", [Total Sales],
"Budget", [Budget Sales],
BLANK()
)
SELECTEDVALUE returns the chosen scenario name when one is picked. When none or several are picked, it returns BLANK, which falls through to the else value.
Things to watch
- The first match wins. Write conditions from the most specific to the least specific.
- Without an else value, unmatched cases return BLANK. Add one when a default matters.
- In the scenario form, the expression is evaluated once for the context, which is efficient. In the TRUE() form, each condition is tested per row, which can be slow on big tables.
- SWITCH cannot change the filter context by itself. It returns a value, and CALCULATE changes filters.
- Text labels from SWITCH are text. Use numbers for any value that a chart or sum needs.
In business terms
SWITCH is how finance builds aging buckets, scenario comparisons, chart of accounts mappings and status labels. It keeps the business rules in one readable list that an auditor or a controller can check line by line.