DAX Measure Switch Based on a Column Value in Power BI

How-to basics · Updated November 3, 2026

Yes. In a DAX measure, use SWITCH to return a different measure for each value, and use SELECTEDVALUE to read the column value in the current filter context. For a report user to choose the result, put that column in a slicer; a disconnected selector table is often the clearest setup.

How a DAX measure switch works

A measure produces a single result for the current filter context. That context can come from slicers, filters, visual rows and columns, or interactions with other visuals. A column, by contrast, can contain many values. A measure therefore needs a way to turn the relevant column value into one scalar value before it can decide which result to return.

SELECTEDVALUE does that when exactly one distinct value is available. SWITCH compares the selected value with the choices you specify and returns the matching expression. The general pattern is:

Measure Choice =
VAR Choice = SELECTEDVALUE('Selector'[Option])
RETURN
    SWITCH(
        Choice,
        "Option A", [Measure A],
        "Option B", [Measure B],
        BLANK()
    )

Each bracketed item such as [Measure A] is a measure reference, not a column reference. The final BLANK() is the fallback result when the selector has no single matching choice. You can replace it with a default measure if that is the intended behavior.

Example: choose a measure with a slicer

Suppose a sales report should let readers switch between sales, order count, and profit margin. First create the underlying measures. With a sales table named Sales containing the indicated columns, the measures could be:

Total Sales = SUM(Sales[Sales Amount])

Order Count = DISTINCTCOUNT(Sales[Order ID])

Total Profit = SUM(Sales[Profit])

Profit Margin = DIVIDE([Total Profit], [Total Sales])

Next, create a small selector table with one text column named Metric. You can use Home > Enter data in Power BI Desktop, entering Sales, Orders, and Profit Margin as its rows. Alternatively, create it with this DAX calculated table:

Metric Selector =
DATATABLE(
    "Metric", STRING,
    {
        {"Sales"},
        {"Orders"},
        {"Profit Margin"}
    }
)

Do not create a relationship between this selector and the sales table. Add Metric Selector[Metric] to a slicer, then create the switching measure:

Selected Metric Value =
VAR MetricName = SELECTEDVALUE('Metric Selector'[Metric])
RETURN
    SWITCH(
        MetricName,
        "Sales", [Total Sales],
        "Orders", [Order Count],
        "Profit Margin", [Profit Margin],
        BLANK()
    )

Put Selected Metric Value in a card or chart value well. When a reader selects Sales, the measure returns [Total Sales]; selecting Orders returns [Order Count]. Existing filters, such as date or region, still affect those underlying measures. The selector table is disconnected deliberately: its selected text is read by the switching measure, while the normal report filters continue to filter the sales data.

Using a column already in the visual

A separate slicer table is not always needed. If a visual groups data by a column, that row or category can provide the value to test. For example, a measure can select different calculations for each category:

Value by Category =
SWITCH(
    SELECTEDVALUE('Product'[Category]),
    "Accessories", [Total Sales],
    "Services", [Total Profit],
    BLANK()
)

In a table visual with one category per row, the row generally filters Product[Category] to one value, so SELECTEDVALUE can return that category. At a total row, or in a card with several categories in context, there may not be one unique category. The measure then returns blank under this example. That behavior is often appropriate, but you can provide an alternate value to SELECTEDVALUE or a final SWITCH result if the total needs defined behavior.

This is a frequent source of confusion: a measure does not automatically iterate through every value in a column. If the calculation must evaluate each row of a table, it may need an iterator such as SUMX, or a different model design. A switch selects a branch based on the scalar value visible in the current context; it is not a row-by-row loop.

Choosing between SWITCH and SWITCH(TRUE())

Use the regular form of SWITCH when one expression is compared with a list of exact choices, such as names in a slicer. It is direct and easy to maintain. DAX evaluates the expression and checks the listed values for a match, returning the corresponding result.

SWITCH(TRUE()) is useful when the branches are conditions rather than equal-value matches. For example, it can classify a score into ranges:

Score Band =
SWITCH(
    TRUE(),
    [Score] >= 90, "High",
    [Score] >= 70, "Medium",
    "Low"
)

Order matters in this pattern: the first true condition wins. Put more restrictive or higher thresholds before broader ones. For a selector containing labels such as Sales and Orders, regular SWITCH is usually clearer than SWITCH(TRUE()).

Handling multiple selections and defaults

By default, SELECTEDVALUE(Column) returns the column value only when there is exactly one distinct value in the current context. If there are zero or multiple distinct values, it returns blank. Its optional second argument supplies an alternate result for those cases, for example SELECTEDVALUE('Metric Selector'[Metric], "Choose one").

Choose the behavior intentionally. A slicer configured to allow multiple selections can make the selected value ambiguous, even if the reader expects the measure to add the chosen metrics together. A switch does not automatically combine branches. To keep the design simple, turn on single select in the slicer’s formatting options, or show a useful message/default when selection is missing or ambiguous. If multiple selections must be supported, define explicitly what the result should mean and build a measure for that behavior.

Formatting and data-type considerations

Branches should return compatible kinds of values. A measure that switches among sales, order counts, and percentages returns numeric results, but those numbers have different meanings and often need different display formats. A single static format can make a valid result misleading—for example, formatting a margin as currency or an order count as a percentage.

Options include using a dynamic format string for the measure where supported by the model and authoring workflow, keeping selections to values with the same unit, or using separate visuals or measures when the formats differ. Dynamic formatting changes how the numeric result is displayed; it does not change the value itself. Avoid converting everything to text merely to add a currency symbol or percent sign unless a text result is truly required, because text is not suitable for many numeric operations and chart axes.

Also check that the labels in the selector match the strings in SWITCH. A typo or an unexpected blank can send the expression to its fallback. Keeping labels in one selector table and documenting the choices makes the measure easier to maintain.

Common problems and how to diagnose them

  • The measure is blank: Check whether the selector has exactly one distinct value, whether that value matches a SWITCH case, and what the fallback returns.
  • The total differs from the visible rows: A total is evaluated in its own filter context; it is not necessarily the sum of the displayed row results. If the total should sum a calculation per category, an iterator may be needed.
  • A column reference causes an error: A measure needs a single scalar value, so use SELECTEDVALUE or another deliberate aggregation rather than referencing a column that has multiple values in context.
  • The slicer filters the fact table unexpectedly: For a pure metric selector, verify that the selector table has no relationship to the fact table. A related field may be appropriate for other designs, but it changes the filtering behavior.
  • Some choices show the wrong format: Review the measure’s format settings and whether the choices mix units. Correct arithmetic does not guarantee an appropriate display format.

When debugging, temporarily create a card with SELECTEDVALUE('Metric Selector'[Metric], "No single choice"). It helps distinguish a selector-context problem from an error in the branch measures. Then test each underlying measure on its own under the same date, category, and other report filters.

When to use a field parameter instead

A disconnected table plus SWITCH is a good choice when you need one measure to return one of a known set of scalar calculations, or when the branch logic has custom rules. Power BI field parameters are an alternative when the goal is to let users swap measures or dimensions in a visual. In Power BI Desktop, create one through Modeling > New parameter > Fields, then add the generated parameter to the visual and use its slicer as needed.

A field parameter changes which field or measure a visual uses; a switch measure calculates and returns a value. Parameters can be more convenient for straightforward visual switching, while a switch offers explicit branching logic. They solve related but not identical problems, so decide whether you need to control a calculation or let a visual substitute fields.

Frequently Asked Questions

Can a DAX measure use SWITCH with a column?
Yes. Read the column as a single value in the current filter context, commonly with SELECTEDVALUE, and use that value as the expression tested by SWITCH.

Why does SELECTEDVALUE return blank?
It returns blank by default when the context contains zero or more than one distinct value. Check the slicer selection and visual context, or provide an alternate result as the second argument.

Does the selector table need a relationship?
Not when it is only a list of choices for a switch measure. Leaving it disconnected lets the measure read the selection without using it to filter the fact table.

Can one measure switch between percentages and currency?
It can return the selected numeric measure, but one static format may not suit both units. Use an appropriate dynamic format string where available, or separate the visuals or measures.

Should I use SWITCH or a field parameter?
Use SWITCH for custom branching inside a measure. Use a field parameter when users should swap fields or measures used by a visual with less custom calculation logic.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.