How To - Switch Measure displayed in a Visual

Owned by Bruce Miller, created
Oct 22, 2020
4 min read

This article describes a method to dynamically change the measure upon which a visual is based. This is based on the idea of a measure being based on a different measure which can be selected by a slicer. This can help reduce the number of columns in a report, or the number of visuals and allow them to serve multiple purposes. For example, in a matrix, a column could serve to compare actuals against budget or against prior year, based on a selector. For a visual, a pie-chart for example, could be switched to display different dimensions, such as FTE, Positions, FTE.

There are a number of articles that describe this technique, one fairly clear article is: https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern

For this technique, in addition to the measures to be displayed, there are four components.

  1. A “selector” table which will contain the text strings to be displayed in a selector (slicer).

  2. A slicer which displays the values from the selector table

  3. A “selected” measure which returns the selected value from the selector table

  4. A “switch” measure which uses the “selected” measure to switch the measure which is being displayed.

Instructions

These instructions assume you already have some existing measures you would like to switch between in a visual. In this example, there are three measures:

Positions = count(emp_position[position_no])
Head Count = sum(emp_position[head_count_max])
FTES = sum(emp_position[fte])

  1. Create a selector table to hold the “selector values”.
    You will need a table to contain the values to be displayed on the slicer which will be used to switch between measures. This table can be imported, for example from an Excel worksheet, or entered in the PBI desktop. To create in the desktop go to the Home/Enter Data tab and click Enter data

    This takes you to the Create Table panel.

    The values you enter will be displayed on the “selector” slicer.
    If you wish to update the table after it is entered go to the Data view/Home Tab, click “Transform Data”, select the Selector table that was created, and hit the gear in the “Source” step in “Applied Steps”.

    for example, here “Program” is changed to “Head Count”

     

  2. Create a measure that will return the selected value to be used on a slicer.
    This step creates a measure which returns the value selected in the table of selections.
    Selected Measure = SELECTEDVALUE(Selector[Value],"FTE")
    Here we provide the default for “FTE”.

  3. Create a measure (the switcher) that uses the selected value measure (here, “Selected Measure”) and switches out the measure used.
    This uses the SWITCH function, which is like a CASE statement.
    Switch Measure = SWITCH([Selected Measure],
    "FTE",[FTES],
    "Program",[Head Count],
    "Positions",[Positions])
    This in effect assigns “Switch Measure” the value of another measure based on the selection.

  4. Create a Slicer using the Selector Table you created and the column (“Value”) which contains the text to display.

    You may want to display this more like buttons, and could set this to the horizontal layout, which can be set for a List Slicer only.

  5. Create the visual using the “Switch Measure” as the value displayed.
    Here we would use the “Switch Measure” as the displayed measure.

    In the example table, all three measures, including the Switched Measure are displayed in the table. The pie-chart displays the selected measure, with the title changing to display the selected measure.

You will probably want to change the title on the visual to be based on the selected measure. To do this set the Title property to be based on the measure

Because the measure name will show when hovering, you might want a more descriptive, yet generic name for the measure, like “Count” or “Applicants”.

 

Taking the selector table to the next level
If you expect to add several switchable measures and don’t want multiple selector tables, you can add a “Selector” column in my selector table. You an then use this as a visual level filter on the visual to display only the desired set of values.

Also, following a suggestion in the article, you may wish to add a code value to use in your DAX. This allows changing the value shown in the selector, without changing the DAX code.

 

Related articles