How to Create a Dynamic Measure in Power BI

You've built a beautiful Power BI report, but your team wants to see sales, profit, and units sold. Do you cram three different charts onto one page, or do you create multiple versions of the same page? There's a much more elegant solution: creating dynamic measures that let users choose which metric to display using a simple slicer.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

This article will show you exactly how to build interactive reports by creating dynamic measures in Power BI. We'll walk through the process step-by-step, using DAX's powerful SWITCH function to give users control over the data they see.

What Are Dynamic Measures, Anyway?

Imagine a single bar chart on your report. A dynamic measure allows you to add a slicer (a set of on-screen buttons) that says "Show me: Sales," "Show me: Profit," or "Show me: Quantity." When a user clicks one of those buttons, the bar chart instantly updates to show the selected metric.

It's all about creating a more interactive and user-friendly experience. Instead of forcing your audience to navigate multiple pages or interpret cluttered visuals, you empower them to explore the data on their own terms. This keeps your dashboards clean, efficient, and much more impactful.

This technique relies on a few core components working together:

• Base Measures: These are the standard measures you already know, like Total Sales or Total Profit.
• A Parameter Table: This is a simple, disconnected table you create that holds the names of the measures you want to switch between. This table will power our slicer.
• A SWITCH Measure: This is the "brain" of the operation. It's a single DAX measure that uses the SWITCH function to check which option the user has selected in the slicer and then returns the corresponding base measure.

Don't worry, it's easier than it sounds. Let's build it piece by piece.

Step-by-Step: Creating a Dynamic Measure in Power BI

For this walkthrough, let's assume you have a sales data table and you want to allow users to switch between viewing Total Sales, Profit Margin, and Total Orders.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Create Your Base Measures

First, we need the individual measures that will be our building blocks. If you already have these, you can skip to the next step. If not, here are some simple examples. To create a new measure, right-click on your data table in the 'Fields' pane and select "New measure."

Total Sales Measure:

Total Sales = SUM(Sales[Revenue])

Total Orders Measure:

Total Orders = COUNT(Sales[OrderID])

Profit Margin Measure:

Profit Margin = (SUM(Sales[Profit])) / (SUM(Sales[Revenue]))

Important Tip: It's best practice to format your measures properly. For Total Sales, set the format to Currency. For Total Orders, set it to a whole number. For Profit Margin, set the format to Percentage.

Step 2: Create a Parameter Table for the Slicer

Now, we need to create a simple table that lists out the names of the measures we just created. This table won't have any relationships with your other data tables, its only job is to provide the values for our slicer.

1. On the 'Home' ribbon in Power BI Desktop, click on Enter data.
2. A "Create Table" window will pop up. Name the first column something like "Measure Name".
3. Under this column, enter the exact names you want users to see in the slicer. For example:
4. (Optional but recommended) Add a second column for sorting. Let's call it "Sort Order." This lets you define the order in which the measures appear in the slicer. Give each measure a number: Total Sales = 1, Profit Margin = 2, Total Orders = 3.
5. Name your table something clear, like MeasureSelection or ParameterTable.
6. Click Load.

If you added the sort column, go to 'Data' view, select the "Measure Name" column, and then on the 'Column tools' ribbon, click Sort by column and choose "Sort Order." This ensures your slicer is always displayed in the intended order.

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

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

Right-click your measures table again and select "New measure." Enter the following DAX code:

Selected Measure = SWITCH( TRUE(), SELECTEDVALUE('MeasureSelection'[Measure Name]) = "Total Sales", [Total Sales], SELECTEDVALUE('MeasureSelection'[Measure Name]) = "Profit Margin", [Profit Margin], SELECTEDVALUE('MeasureSelection'[Measure Name]) = "Total Orders", [Total Orders], [Total Sales] // This is the default value if nothing is selected )

Let's break down that DAX formula:

• SWITCH(TRUE(), ...): This is a common pattern. It tells DAX to evaluate each line that follows and stop when it finds the first one that is TRUE.
• SELECTEDVALUE('MeasureSelection'[Measure Name]) = "Total Sales": This line checks if the value "Total Sales" is currently selected in our slicer.
• , [Total Sales]: If the line above is TRUE, the formula returns our existing [Total Sales] measure.
• The formula repeats this logic for "Profit Margin" and "Total Orders."
• , [Total Sales]: The final line at the end is a fallback. If nothing at all is selected in the slicer, the SWITCH function will return this default measure. It's always a good idea to have a default in place.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Put It All Together on the Report Canvas

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

1. Add the Slicer: Drag the "Measure Name" column from your new MeasureSelection table onto the report canvas. In the 'Visualizations' pane, select the Slicer visual. To make it look more like buttons, go to the 'Format your visual' settings, open 'Slicer settings', Style, and choose "Tile."
2. Add Your Chart: Add a visual to the canvas, like a column chart. For the X-axis, let's use a date field, like Month.
3. Use the Dynamic Measure: Now, for the Y-axis, drag your new [Selected Measure] into the Y-axis field well. Do not use your original base measures here.

That's it! Click the buttons in your new slicer. You should see the chart instantly update to show Total Sales, then Profit Margin, then Total Orders. You've successfully created a dynamic report.

Going Further: Advanced Tips for Dynamic Visuals

Once you've mastered the basics, you can add more layers of polish to make your reports even more professional.

Dynamically Changing Chart Titles

When the measure changes, the chart title should change too. A static title like "Sales Over Time" is inaccurate when the chart is showing "Total Orders." Let's create a dynamic title.

Create one last measure:

Dynamic Chart Title = SELECTEDVALUE('MeasureSelection'[Measure Name], "Overall Performance") & " by Month"

This measure grabs the selected value from the slicer. If nothing is selected, it defaults to "Overall Performance." Now, click on your chart, go to the 'Format your visual' pane, open 'General' -> 'Title', and click the fx button next to the 'Text' field. In the dialog box, select your [Dynamic Chart Title] measure. Now, your chart's title will update automatically!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Handling Heterogeneous Measures

What happens when you mix formats, like Currency ($), Percentages (%), and Whole Numbers? In our example, Profit Margin is a percentage while Total Sales is currency. If your dynamic measure (Selected Measure) has its format set to 'General', your Profit Margin might show up as 0.05 instead of 5%.

The best way to handle this is with the FORMAT function, but it converts your numbers to text. This means you can't use it directly in a column/bar chart's values (which require a numeric type). This technique is best suited for Cards or Table visuals.

Here's an advanced version of the [Selected Measure] for a Card visual:

Selected Measure Formatted = VAR SelectedMetric = SELECTEDVALUE('MeasureSelection'[Measure Name]) RETURN SWITCH( TRUE(), SelectedMetric = "Total Sales", FORMAT([Total Sales], "$#,##0"), SelectedMetric = "Profit Margin", FORMAT([Profit Margin], "0.0%"), SelectedMetric = "Total Orders", FORMAT([Total Orders], "#,##0"), FORMAT([Total Sales], "$#,##0") )

As you can see, Power BI provides the tools, but building truly dynamic reports requires learning several layers of DAX logic, creating disconnected tables, and handling formatting issues one by one.

Final Thoughts

Creating dynamic measures frees your reports from being static, one-dimensional displays of data. By giving users the power to choose what they see with slicers, you create an interactive dashboard that encourages exploration and delivers deeper insights without cluttering the screen.

Learning the DAX and data modeling needed for Power BI is a powerful skill, but it often involves a steep learning curve and hours of configuration. That's why we built Graphed. We connect directly to your data sources - like Google Analytics, Shopify, and Salesforce - and let you create dynamic dashboards and ask follow-up questions using plain English. Instead of writing complex SWITCH statements and formatting DAX, you can simply ask, "Show me a chart comparing my total sales, total orders, and profit margin by month," and watch it get built instantly.