Dynamic measures in Power BI allow users to create flexible and interactive visuals by dynamically switching calculations based on user input. In this tutorial, we’ll learn how to create dynamic measures and use them with filters to enhance your dashboards. We’ll use the Sample Superstore dataset, with the table renamed as salesdata.

Get – Sample Superstore Dataset

---

Understanding Dynamic Measures

Dynamic measures enable end-users to select a metric (e.g., Sales, Profit, or Quantity) using a slicer, and Power BI updates the visualizations based on their selection. This eliminates the need for multiple visuals and enhances user experience.

Let’s create dynamic measures and filters step-by-step:

1. Dataset Setup

Ensure you’ve loaded the Sample Superstore dataset into Power BI and renamed the table to salesdata. The table should include columns for Sales, Profit, and Quantity.

2. Creating a Measure for Dynamic Switching

Define a measure that changes its value based on the user’s selection. Use the following DAX formula for the measure:

Load Value =
    SWITCH(
        [Selected Measure],  
        "Total Sales", SUM(salesdata[Sales]),
        "Total Profit", SUM(salesdata[Profit]),
        "Total Quantity", SUM(salesdata[Quantity])
    )

• SWITCH: A DAX function that evaluates an expression against multiple values and returns the result for the first matching value.
• SUM: Aggregates values for the specified column.

3. Creating a Table for User Selection

Create a table to allow users to select a measure dynamically:

1. Go to the Modeling tab.
2. Select New Table.
3. Enter the following DAX formula:

Select Measure = {
    ("Total Sales", NAMEOF('salesdata'[Total Sales]), 0),
    ("Total Profit", NAMEOF('salesdata'[Total Profit]), 1),
    ("Total Quantity", NAMEOF('salesdata'[Total Quantity]), 2)
}

This creates a table with three rows representing the selectable measures.

4. Setting Up a Slicer

1. Add a slicer to your report.
2. Drag the field created in the Select Measure table into the slicer.
3. Configure the slicer to display the measure names (e.g., Total Sales, Total Profit, Total Quantity).

5. Filtering with Dynamic Measures

Filters further enhance your dashboard by narrowing down the data displayed. For instance, you might want to show the dynamic measure filtered by a specific category.

Example: Applying a Category Filter

1. Add a column for Category from the salesdata table to the report.
2. Use a slicer or filter pane to enable filtering by category (e.g., Furniture, Office Supplies, Technology).
3. The dynamic measure will automatically update based on both the user’s selection of the measure and the category filter applied.

6. Creating Visuals

Now that everything is set up, you can create dynamic visuals:

• Add a card visualization to display the selected measure’s total value.
• Add bar or column charts to break down the dynamic measure by other dimensions like Region, Sub-Category, or Category.

7. Advanced Tips

• Formatting: Use conditional formatting to highlight changes in dynamic measures.
• Default Selection: Set a default value in the slicer for a better user experience.
• Validation: Ensure all visuals respond dynamically by checking interactions between slicers and charts.

---

Conclusion

Dynamic measures in Power BI unlock powerful customization and interactivity for dashboards. By combining them with filters, you can create a seamless and engaging experience for users. Try implementing this technique with the Sample Superstore dataset to see how it transforms your reports!