Circuit breaker measurement history
Using a measure of change to toggle results is a common and mature technique used in Power BI and Power Pivot for Excel. For example, a measure of change can be used to toggle what appears on a graph so that the end user can easily change the data that is displayed (see image below).
This technique uses a disconnected table, a slicer to receive user selection, and a change measure to change the measurement result based on user input. When you select an option on the slicer, you can have your graph update to show the result you want to see on the graph. I blogged about this in 2014. here. This is what the measurement of the switch above looks like.
While this technique is useful, there has been a limitation with this approach; the final measure can only be in a single number format.
For example, if you have the following 3 measures that require a different format, your chart may display the values based on the selection, but the axis format will not change in response to the different measure.
- Total Sales - Currency
- Total number of products: whole number
- Var vs LY% - Percentage
I created an idea on the Power BI ideas site in 2016 to allow format based on a selection in the Switch measure. The idea received more than 3,000 votes. It is now possible to solve this problem by using calculation groups instead of switching measures. In my blog post today, I'll show you how.
Measures in the data model
I am using the Adventure Works database again for this article. And as shown below (# 1), I have 4 measurements with 3 different formats (I will use only 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 measurement technique, the next step would be to create a disconnected table for the cutter. 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 how to do it in my article on installing external tools for Power BI.
Under External Tools (# 1 below), select Tabular Editor (# 2). If you don't see these menu options in Power BI Desktop, read my article linked above.
The Tabular Editor opens and you can see the tables in your data model under Tables in the left pane.
Create a calculation group
Right-click the Tables folder (# 1 below), click Create New (# 2), and then click Calculation Group (# 3).
I named the new Calculation Group as Chart Item (# 1 below).
In the calculation group chart element (# 1 below), create calculation elements. Right-click Calculation Items (# 2), and then click 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 measuring the switch using the old technique:
- Right-click on the "Chart Element" calculation group, then click Create New and then Measure.
- Name the measure as Selected Chart Element (# 1 below)
- Add the special DAX function SELECTEDMEASURE () for the Selected Chart Element measure as shown below (# 2).
Next, for each of the calculated items (# 1 below), add the corresponding measure (# 2) as an expression (# 3). There is no IntelliSense at the time of writing, and you must take care to write the measures exactly as they appear in the data model. Then click Save (# 4).
Let's pause here and take a look at how it works.
You'll see a new table called "Chart Element" that contains a single column and a single measure in Power BI (# 1 below). If you've ever used change measures in this way, you'll recognize that this new table, column, and measure give you the same experience you'd be used to.
I added the 'Chart Item' [Name] column to the cutter (# 2 below) and the [Selected Chart Item] measure to the line chart (# 3 below). Then I added the Calendar [MonthName] to the x-axis chart axis and set a filter to End. Year = 2004.
Note that at this stage, the bicycle sales % format is decimal format (# 4 above). This is the original problem I referred to earlier, and this problem can now be solved with Calculation Groups.
- Return to the 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 “String Expression Format” (# 5 below) and paste the Format of 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 elements.
- Save your changes and close the Tabular Editor.
One thing to keep in mind here. The Expression format string (# 6 above) should be in quotes, but it is not in quotes shown in # 3 above - confusing, I know. The reason for the difference is that n. 3 above is a property of the measure. The # 6 format expression string is actually a DAX formula that should return a text string. This is actually very powerful because you can specify a static string like "#, ## 0", but you can also write a DAX measure that conditionally returns different text strings, like IF ([Some Measure] = "Some Condition", format type 1 ″, ”format type 2 ″). Hope that makes sense. You can read Kasper blog article here if you want more information
Using the calculation group to dynamically filter the chart
As shown below, notice that the axis of the chart now updates to reflect the correct number format.