How to apply #dynamic measures in Power BI

Sometimes, to avoid overcrowding a dashboard, using dynamic measures is an effective approach. With them, you can easily switch the same visual or even an entire dashboard across multiple measures and/or formattings.

Method 1: Custom table

In the chart below, we are showing the “Total Miles” measure split by “State” and “Trip Type”. I want to show two other measures “Loaded Miles” and “Loaded Miles Ratio” on the same visual. So let's see how to achieve this using dynamic measures.

Let’s create all three measures using DAX.

Total Miles = SUM(Trips[TotalMiles])
Loaded Miles = SUM(Trips[LoadedMiles])
Loaded Miles Ratio = DIVIDE([Loaded Miles],[Total Miles],0)

Add a custom table to provide options for letting users select “Measure Names”, also include the “Order” column for sorting names.

Custom table with user input

Create a dynamic measure that can switch between three measures based on the user selection.

Dynamic Measure =
SWITCH (
    VALUES ( 'Measures List'[Measure Name] ),
    "Total Miles", [Total Miles],
    "Loaded Miles", [Loaded Miles],
    "Loaded Miles Ratio", [Loaded Miles Ratio]
)

Now, go back to the visual and replace “Total Miles” with this dynamic measure. Add a slicer on top of the visual to show the list of measure names from the custom table. To sort the measure names on the slicer, change the sort order for “Measure Name” to use “Measure Order”.

We are almost done, except for two formatting changes.

Formatting 1 — Disable the y-axis title since there is currently no support for adding an expression here. However, you can set a visual title using a measure.

Dynamic Title = 
VALUES('Measures List'[Measure Name]) & " by Origin State and Trip Type"

Formatting 2 — When the “Loaded Miles Ratio” is selected, values are shown as decimals instead of percentages. This is because all measures are formatted the same way. To set dynamic formatting, select “Dynamic Measure”, go to the Format menu, and select the Dynamic option.

Then set the formula bar to Format and enter below DAX expression

SWITCH (
    VALUES ( 'Measures List'[Measure Name] ),
    "Total Miles", "#,0.00 K",
    "Loaded Miles", "#,0.00 K",
    "Loaded Miles Ratio", "0.00%"
)

Finally, you have a visual that can switch measures dynamically along with their applicable formats.

Method 2: Filed Parameters

In this method, we will use a field parameter instead of a custom table. To do this, open the New parameter dropdown under the Modelling menu and select Fields.

In the next dialog box, select our three measures and click Create.

After this, change the expressions of dynamic measure, dynamic title, and format to use the newly created field parameter “Measures Parm”.

Dynamic Measure =
SWITCH (
    VALUES ( 'Measures Parm'[Measures Parm] ),
    "Total Miles", [Total Miles],
    "Loaded Miles", [Loaded Miles],
    "Loaded Miles Ratio", [Loaded Miles Ratio]
)

Dynamic Title = 
VALUES('Measures Parm'[Measures Parm]) & " by Origin State and Trip Type"

<<Format>>
SWITCH (
    VALUES ( 'Measures Parm'[Measures Parm] ),
    "Total Miles", "#,0.00 K",
    "Loaded Miles", "#,0.00 K",
    "Loaded Miles Ratio", "0.00%"
)

Finally, you have the visual that can switch between three measures.

Method 3: Bookmarks

This method doesn’t involve DAX. We will use a series of bookmarks to switch between different views (measures).

First, create three Blank buttons and change their Text to our measure names. Create three copies of the same bar chart (one for each measure) and stack them one on top of another.

If you open the Selection panel, you can see three charts and three buttons. Change names for each button to match their text.

Open the Bookmarks panel, and create three bookmarks of the same page. Change the name of the bookmarks to match our measure names. On the first “Total Miles” bookmark, hide the “Loaded Miles” and “Loaded Miles Ratio” bar charts to show only “Total Miles” bar chart. Do likewise for the other two bookmarks as well.

Finally, go to Actions of each button and select the corresponding bookmark and you are done. To test this you can ctrl+click on each button and see the bar chart switching measures on the y-axis.

Conclusion — If you are not familiar with DAX expressions, you can choose method 3. However, this method can become difficult to maintain if you have a lot of visuals on the dasbhaord. Method 1 and 2 are almost identical with 2nd being a simplified version of 1st, thanks to intoduction of field parameters, though it is still a preview feature. I will prefere Method 2 as it is easy to maintian and doesn’t require custom sorting.

Sample files can be downlaoded for here

Seek more articles, you must. Follow, you can! -Yoda

Enridge - Medium