Downloads
DAXLogicalIntermediate

SWITCH

Compares an expression with a list of values and returns the result for the first match.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals