Dynamic Formatting of Switch Measures - Excelerator BI

Dynamic Formatting of Switch Measures

History of Switch Measures

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).

Original Switch Measure

 

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.

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.

Tabular Editor

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.

Dynamic Formatting Of Switch Measures

24 thoughts on “Dynamic Formatting of Switch Measures”

  1. Hi Matt,

    Thanks for the instructions, have been using this on a model im working at.
    One unfortunate result though is that date-measures and time intelligence in general is completely out of order when tabular editor for conditonal formatting.

    After setting up the tabular editor the TODAY() formula returns “General Da e” insted of todays date. “General Date” is the title of the standard out of the box formatting of TODAY(). If i change the formatting to i.e. (mmmm yyyy) it returns 0000 2020.

    Are you (or anyone else reading this) experiencing the same issue around dates when using tabualr editor for conditional formatting?

  2. Hi Matt,

    First of all, thank you very much for this concept and it has helped me immensely. I see that your chart has only one line item ie. Sales that then dynamically changes as per the different format in the Chart Name. How do I go about this if I wanted a second line in the lin chart , let’s say Target Sales line? How do I dynamic measure formatiing for two or more lines in the chart ?
    Thank You

  3. Great step by step. I got this to work as far as the formatting, but I’m using it on a combo bar/line chart with the line on a secondary y-axis. I only want the slicer to apply to the line but it also changes the bar to the selected measure, even though I have a static measure dragged over to the bar series. Previously, just using the switch method, this wasn’t happening. Any idea what might be causing this?

    thanks

    1. Assuming you have a different measure for the bar chart that is not related to the slicer, then no, I have no idea what can be happening. If I were testing this, I would switch it to a matrix to see what is happening to the numbers.

  4. Hi Matt,
    Thanks for a very clear instruction. Very helpful. I have been using switch for a while, and the formatting problem has bothered me.

    Now I have a slightly more complex setup with several measures depending on one selection and I am not sure if it is possible to do this using calculation groups. I would be grateful if you could point me in the right direction!

    I have two graphs on one page: one showing selected_measure_median and the other showing selected_measure_q90

    I need to somehow set both of these properties with one selection.. preferably using calculation groups and I can’t seem to figure it out

    This is my current setup using switch (that I want to get rid of)

    Metric Selected Index = SELECTEDVALUE(‘Metric Selection'[Metric index],BLANK())

    Selected Median Metric =
    SWITCH( TRUE(),
    [Metric Selected Index] = 1, [sig_1 median],
    [Metric Selected Index] = 2, [sig_2 median],
    [Metric Selected Index] = 3, [sig_3 median], BLANK())
    ————————-
    Selected Q90 Metric =
    SWITCH( TRUE(),
    [Metric Selected Index] = 1, [sig_1 q90],
    [Metric Selected Index] = 2, [sig_2 q90],
    [Metric Selected Index] = 3, [sig_3 q90], BLANK())

      1. Ah. Sorry. sig_1 and sig_2 needs to be displayed with 2 decimal positions (values typically doubles like 0.21, 1.56, etc). sig_3 is an integer that I want to show without any decimals (typical values 345, 123, 45)

        The actual problem is that I show them not in a graph but in a matrix with many columns and sig_3 shows like 345.00, 123.00 etc. Beside from being ugly, it eats up all my screen estate and end user has to scroll horizontally.

  5. Pretty cool. Was it not you who posted the suggestion of individual formatting measures inside a SWITCH statement?

  6. Hello Matt,

    I love this post, and decided to apply your tutorial to a table matrix visual rather than a chart.
    The [Name] is in that case put in columns rather than used as a slicer, and I have another dimension in rows.
    That works perfectly well, until I try to apply conditional font color formatting based on a field value.
    I have a measure that returns the right color depending on the value in context.
    While this one works well when I use the old switch and disconnected table method, it totally fails with the calculated items method.
    Instead of returning a color, it returns the value of the equivalent of your [Selected Chart Item], which of course cannot be interpreted as a color.
    There is obviously something I don’t understand with the calculation items (that I use for the first time).

    Any suggestion is welcome!

  7. Thanks! We use the selected chart feature in almost every report where I’m working. I’ll be bringing this article to Friday’s meeting where we were going to discuss Tabular Editor. This is a great way to show the power of the external tools in a tangible example for us.

  8. Hi Matt!
    Thank you for the cool walkthrough article!

    However, this solution has some drawbacks… For example, if we want to add some another measure into the current report page – it will break. IMHO, we still should use the SWITCH function.

    I created an article earlier about problems when using calculation groups for measures switch – you can find it here if you’re interested
    https://rollingaverage.com/set-up-measure-selection-with-dynamic-formatting-in-powerbi/

      1. Hmm… Yes, you’re right, my mistake. SWITCH is not mandatory.
        But in any case, we need to use ISSELECTEDMEASURE () to somehow differentiate between measures we want to calculate/format using calculation group and other measures in the report page

        1. To use a switch measure, you need a switch statement and a disconnect table. Both must be in sync, so if you want to add something, you have to edit the switch measure and you have to add a new record to the disconnected table. If you use the approach I have covered here, you have to add a new calculation item instead. The use of selectedmeasure() is a one off substitute for the switch measure – no changes needed. The calculation time is a substitute for the new item in the disconnected table.

    1. Short answer – I don’t know. My guess is not at this time. The reporting services version lags between 2-6 months behind the online version. I think the RS version is released every 4 months. I think there is one due in Sept, so keep an eye out for that.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top