Select Distinct Logo Clear Background

Business Analytics Blog

Toggle Measures in Power BI

Toggle Measures in Power BI

Space on your dashboards is a limiting factor

Setting up toggle measures in Power BI helps to use the space better

One of the key challenges with dashboard building is finding the balance between detail and available space

This article shows you how to combine a couple of features to create the ability to toggle measures in Power BI

You may want to show sales amount, units sold or profit depending on the selection of a slicer.

We want to give the user an easy option to switch between measures using a slicer

Dynamic Measure Slicer in Power BI
This will allow the users to toggle between measures to see which of these measures they choose without having to squeeze all of the data on screen at the same time

 

How can you achieve this in Power BI?

One way to do this is to use the SWITCH function, which is a kind of logical function, similar to CASE statements in SQL or nested IF statements, that evaluates an expression against a list of values and returns one of multiple possible result expressions.

 

The syntax of the SWITCH function is:

 

SWITCH(<expression>, <value>, <result> [, <value>, <result>]… [, <else>])

The parameters of a SWITCH function are:

  • expression: Any DAX expression that returns a single value
  • value: A target value
  • result: The output to be calculated if the target value is met
  • else: An alternative value if none of the target values are met

Let’s see how we can use the SWITCH function to toggle measures in Power BI.

Step 1: Create a slicer with three options

First, we need to create a slicer that will allow us to choose between three options: Sales, Units Sold and Profit.

Create the list of options for the slicer

Begin by adding a new table to store the names of each measure you want to select from

On the data pane, click enter data

Double click the first column name and edit it to ‘Measure Name’

and add a second column to define the sort order you want the rows to appear on a slicer, we want Sales to appear first so we have set that as option number one

 

Create a list of options for a toggle slicer in Power BI

 

Then name this table ‘Measure List’ and click load

 

Step 2. Add the slicer for the toggle switch

Click on the slicer icon and a slicer is added to the canvas

Drag the newly created field ‘Measure Name’ to the slicer

By default the Measure names will be sorted in value order, but we want to sort by the Order column

To resolve this, go to the data pane, select the measure names field and set it to sort by the order column

 

Sort measure name by the order field in toggle switch in Power BI

Your slicer should look like this

Dynamic Measure Slicer in Power BI

The slicer now has the options in the correct order, but it does not have any purpose as yet

 

Step 3: Define the rules for the SWITCH

 

We added a new measure for the chart which is controlled by the slicer

Add a new measure and insert this DAX code

 

Dynamic Measure =

SWITCH (

TRUE (),

SELECTEDVALUE('Measure List'[Measure Name]) = "Sales", sum('Sales Summary'[ Sales]),

SELECTEDVALUE('Measure List'[Measure Name]) = "Units Sold", sum('Sales Summary'[Units Sold]),

SELECTEDVALUE('Measure List'[Measure Name]) = "Profit", sum('Sales Summary'[Profit]),

0

)

The formula does the following:

  • It creates a variable called SelectedValue that stores the value of the selected option in the slicer using the SELECTEDVALUE function.
  • It uses the SWITCH function to return either sales amount, units sold or profit based on the value of SelectedValue.
  • if the SelectedValue does not match any of the options then the ELSE case at the end is set to return a zero

 

Now we can use the measure in a visual

Add the Dynamic Measure field to the Y axis on a column chart, just as you would if you wanted to chart sales by month

 

Toggle Slicer Showing Units Sold in Power BI

 

Toggle Slicer showing Sales in Power BI

 

The column chart presents this quite well,

but you can use this with any visual that supports measures instead

 

Conclusion

In this blog post, we learned how to use the SWITCH function to toggle measures in Power BI.

We saw how we can combine this with a slicer with three options and use it as a toggle for the SWITCH function.

Our experience of using this for our clients has often been met with great feedback such as “I love that toggle button!” or “I never realised you could switch between measures”

 

 

 

Subscribe to our channel to see more tips and timesavers

Select Distinct YouTube Channel

Or find other useful SQL, Power BI or other business analytics timesavers in our Blog

Our Business Analytics Timesavers are selected from our day to day analytics consultancy work.  They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.

Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!

Business Analytics Blog

By Simon Harrison

Simon Harrison Founder of Select Distinct Limited and a business intelligence expert