Dynamic Measures with SWITCH & SELECTEDVALUE

The Three-Part Pattern

Creating a measure switcher involves three core components working together. Understanding this pattern is key, as you will use it frequently.

1. Base Measures: These are the individual calculations you want to switch between (e.g., a measure for total sales, another for total quantity).
2. A Disconnected "Parameter" Table: This is a simple, standalone table that contains the names of your base measures as text. This table's only job is to populate a slicer, giving the user a list of options to choose from.
3. The "Master" Dynamic Measure: This is the main DAX measure that uses SELECTEDVALUE to read the user's choice from the slicer and a SWITCH function to return the result of the correct base measure.

To get a high-level overview of this process, let's start with a short reading.

Now, let's build this step-by-step in your Power BI file.

Step 1: Create Your Base Measures

First, we need the individual measures that will serve as our building blocks. You should already have [Total Sales] from our previous lessons. Let's create two more for this exercise: [Total Quantity] and [Profit Margin].

1. Create a measure for Total Profit:

   Total Profit = 
   SUM(Sales[SalesAmount]) - SUM(Sales[TotalProductCost])
   

2. Create a measure for Total Quantity:

   Total Quantity = 
   SUM(Sales[OrderQuantity])
   

3. Create a measure for Profit Margin (as a percentage):

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

   Important: After creating the [Profit Margin] measure, select it in the Fields pane and use the Measure tools ribbon to format it as a Percentage. This will be important later.

Step 2: Create the Disconnected Parameter Table

Next, we need a table to provide the options for our slicer. Slicers work on table columns, so we can't add our measures to them directly. Instead, we create a table that holds the text names of our measures.

The cleanest way to do this is with a DAX expression. Go to the Modeling tab and click New Table.

Enter the following DAX code to create the table:

Measure Selection = 
DATATABLE(
    "Measure Name", STRING,
    "Sort Order", INTEGER,
    {
        { "Total Sales", 1 },
        { "Total Quantity", 2 },
        { "Profit Margin", 3 }
    }
)

Why the 'Sort Order' column? By default, Power BI sorts text alphabetically. This column gives us explicit control over the order in which the measures appear in our slicer. To apply it:

1. Go to the Data View.
2. Select the Measure Selection table.
3. Click on the Measure Name column to select it.
4. In the Column tools ribbon, click Sort by column and choose Sort Order.

The following video demonstrates creating this disconnected table.

Step 3: Write the "Master" Dynamic Measure with SWITCH

This is where the magic happens. We'll write one measure that acts as a switchboard, directing Power BI to the correct base measure based on the user's slicer selection.

This master measure uses SELECTEDVALUE to read the slicer and the SWITCH function to evaluate the selection. SWITCH is a powerful and readable alternative to writing complex, nested IF statements. The common pattern is SWITCH(TRUE(), <condition1>, <result1>, <condition2>, <result2>, ...).

Watch how this is done in the next segment of the video.

Now, create this measure in your own file. Let's name it Selected Measure:

Selected Measure = 
VAR SelectedMeasureName = SELECTEDVALUE('Measure Selection'[Measure Name], "Total Sales")
RETURN
    SWITCH(
        TRUE(),
        SelectedMeasureName = "Total Sales", [Total Sales],
        SelectedMeasureName = "Total Quantity", [Total Quantity],
        SelectedMeasureName = "Profit Margin", [Profit Margin],
        [Total Sales] // Default value if something goes wrong
    )

Let's break this down:

• VAR SelectedMeasureName = SELECTEDVALUE(...): We capture the user's selection into a variable. We provide "Total Sales" as a default if nothing or multiple items are selected.
• RETURN SWITCH(TRUE(), ...): We use the SWITCH(TRUE()) pattern to check multiple conditions.
• SelectedMeasureName = "Total Sales", [Total Sales]: If the selected value is the text "Total Sales", the formula returns our [Total Sales] measure. This is repeated for the other measures.

Step 4: Putting It All Together on the Report Canvas

With all the pieces built, it's time to build the interactive visual.

1. Add a Slicer to your report page. Drag the Measure Selection[Measure Name] field into it.
2. Add a visual, such as a Clustered column chart.
3. On the chart's X-axis, add Product[Category].
4. On the chart's Y-axis, add your new [Selected Measure].

That's it! Now click the buttons in your new slicer. You should see the chart instantly update to show Total Sales, then Total Quantity, then Profit Margin.

Advanced Topic: Handling Different Number Formats

You may have noticed a problem: when you select "Profit Margin", the chart shows values like 0.45 instead of 45%. This is because a single measure ([Selected Measure]) can only have one format type (e.g., General, Currency, or Percentage).

The solution is to use the FORMAT function. However, there's a critical catch: FORMAT converts your number into a text string. This means a formatted measure cannot be used on the axis of a bar or line chart, but it works perfectly in Card, Table, and Matrix visuals.

Let's create a second, formatted version of our dynamic measure specifically for use in a Card visual.

Selected Measure Formatted = 
VAR SelectedMeasureName = SELECTEDVALUE('Measure Selection'[Measure Name], "Total Sales")
RETURN
    SWITCH(
        TRUE(),
        SelectedMeasureName = "Total Sales", FORMAT([Total Sales], "$#,##0"),
        SelectedMeasureName = "Total Quantity", FORMAT([Total Quantity], "#,##0"),
        SelectedMeasureName = "Profit Margin", FORMAT([Profit Margin], "0.0%"),
        FORMAT([Total Sales], "$#,##0") // Default
    )

Now, add a Card visual to your report and use this new [Selected Measure Formatted] as its field. As you click the slicer, you will see the card display a perfectly formatted value for each selection.

Conclusion

Congratulations! You have just implemented one of the most valuable patterns in Power BI development. By combining base measures, a disconnected table, SELECTEDVALUE, and SWITCH, you can create highly interactive and clean reports that empower users to explore data on their own terms.

Key Takeaways:

• Dynamic measures rely on a three-part pattern: base measures, a disconnected table for a slicer, and a master SWITCH measure.
• The SWITCH(TRUE(), ...) pattern is a highly readable and efficient way to handle multiple conditional outcomes.
• SELECTEDVALUE is the bridge that connects the user's slicer selection to the logic in your SWITCH function.
• Handling different number formats (like currency and percentages) requires the FORMAT function, which converts the output to text and is best used in cards and tables.

Preview of the Next Lesson:

You've learned how to let users choose what they see. In our next lesson, we'll explore another powerful dynamic technique: filtering. You will learn to use the TOPN function to build reports that automatically show the "Top 10 Products by Sales" or the "Bottom 5 Regions by Profit," allowing users to focus on the most significant data points.