History of Switch Measures in Power BI
Using a switch measure to toggle results is a mature and common technique used in Power BI and Power Pivot for Excel. For example, a switch measure can be used to toggle what appears on a chart so that the end user can easily switch the data being visualised (see image below).
This technique uses a disconnected table, a slicer to receive the user selection, and a switch measure to change the result of the measure based on user input. When you select an option on the slicer you can have your chart update to show the result you want to see in the chart. I blogged about this in 2014 here. This is what the switch measure from above looks like.
While this technique is useful, there has been a limitation with this approach; the final measure can only have a single number format.
e.g. if you have the following 3 measures that require different formatting, your chart can display the values as per the selection, but the axis format will not change in response to the different measure.
- Total Sales – Currency
- Total number of Products – Whole number
- Chg vs LY % – Percentage
I created an idea in Power BI ideas site in 2016 to allow formatting based on a selection in the Switch measure. The idea received more than 3,000 votes. It is now possible to solve this problem using Calculation Groups instead of switch measures. In my blog article today, I am going to show you how to do that.
Limitations of Calculation Groups
Before starting, there are some limitations you may want to consider. They are all listed in the official documentation, right down the bottom of the page. Noteably, calculation groups will stop implicit measures working. You will no longer be able to drag a random column to a visual and aggregate the number. Instead you will have to write measures for everything (just like Analyze in Excel). It is good practice to do this anyway, and you can use Tabular Editor to script that process too.
Measures in the Data Model
I am using Adventure Works database again for this article. And as shown below (#1) I have 4 measures with 3 different formats (I will use just 3 of these in my demo below).
Set up the Report
First, I placed a chart and a slicer on the report canvas as shown below.
Using the old switch measure technique, the next step would be to create a disconnected table for the slicer. The new process will use a Calculation Group instead of the disconnected table. To do this, you must have Tabular Editor installed on your computer. You can read more about doing that in my article about installing external tools for Power BI.
Under External Tools (#1 below) select Tabular Editor (#2). If you do not see these menu options in Power BI Desktop, read my article linked above.
Tabular Editor opens and you can see the tables in your data model under Tables in the left panel.
Create a Calculation Group
Right-click on Tables folder (#1 below), click on Create New (#2) and then on Calculation Group (#3).
I named the New Calculation Group as Chart Item (#1 below).
In the Calculation Group Chart Item (#1 below), create Calculation Items. Right-click on Calculation Items (#2) and then click on New Calculation Item (#3).
I created 3 Calculation Items as shown below (#1).
The next step is to create a measure for the calculation group. This used to be managed by the switch measure using the old technique:
- Right-click on the Calculation Group “Chart Item” and then click on Create New and then on Measure.
- Name the measure as Selected Chart Item (#1 below)
- Add the special DAX function SELECTEDMEASURE() for the measure Selected Chart Item as shown below (#2).
Next for each of the Calculated Items (#1 below), add the corresponding measure (#2) as expression (#3). There is no IntelliSense at the time of this writing and you should be careful to write the measures exactly as they appear in the data model. Next, click on Save (#4).
Let’s pause here and go and take a look at how it works.
You will see a new table called “Chart Item” containing a single column and a single measure in Power BI (#1 below). If you have ever used switch measures in this way, you will recognise this new table, column and measure deliver the same experience you would be used to.
I added the ‘Chart Item'[Name] column to the slicer (#2 below) and the [Selected Chart Item] measure to the line chart (#3 below). Next I added the Calendar[MonthName] to Axis of the chart x axis and set a filter on Fin. Year = 2004.
Notice that at this stage, the format of the % Sales from bikes is decimal formatting (#4 above). This is the original issue I referred to above, and this issue can now be solved with Calculation Groups.
- Switch back to Tabular Editor.
Next, navigate to the original Total Sales measure (#1 below), find “Format String” (#2 below) and copy the format (#3 below). Then navigate to the Calculation Item (#4 below), find the property “Format String Expression” (#5 below) and paste the Format String Expression (#6 below). [Note: you can type the string without cut and paste if you know the correct syntax to do so – I just think it is easier to cut and paste.]
- Repeat for the other 2 calculation items.
- Save the changes and close Tabular Editor.
One thing to note here. The format string Expression (#6 above) must be enclosed in quotes, but it is not in quotes shown in #3 above – confusing, I know. The reason for the difference is that #3 above is a property of the measure. The format expression string #6 is actually a DAX formula that must return a text string. This is actually very powerful because you can specify a static string like “#,##0”, but you could also write a DAX measure that conditionally returns different text strings, like IF([Some Measure] = “some condition”,”format type 1″,”format type 2″). I hope that makes sense. You can read Kasper’s blog article here if you would like some more information
Using the Calculation Group to Dynamically Filter the Chart
As shown below, note the chart axis now updates to reflect the correct number format.