I was on the Power BI Community where a question was asked how to use a Slicer to show different measures in a chart. I personally do it all the time, but after some searching trying to find a potential blog post to reference, I could not find anything suitable. So here is how to do it.

Example

  • I created the following dataset below, which has got Sales and Costs amounts.
  • I then created the following 3 measures as shown below.
    • NOTE: The reason I created the measures, is it is recommended best practise to always use measures. Which as you will see below makes additional measures easier to write.
  • Measures
    • Sales = SUM(Sales[Sales Amount])
    • Costs = SUM(Sales[Cost Amount])
    • Margin = [Sales] – [Costs]
  • So once created this is what my table looked like

Creating the Disconnected Table

Next what I had to do was to create my disconnected table.

What the disconnected table does, is it is a way for me to store my values that I want to display in my slicer.

The reason that it is called a disconnected slicer is that it is not connected to anything. And it is used for the slicer, as well as for my measure to control which measure to show.

  • I clicked on Enter Data and put in the following as shown below.
  • I gave it the name of Measure Selection
  • You will also see above I put in an Order column, this is so that I can control the order of the measures shown in the slicer.
    • Once the table has loaded I clicked on the Measure Name column, then in the Ribbon I went to Modeling and then clicked on the Sort By Column and selected Order

Creating my dynamic measure using the Disconnected table (Measure Selection)

In the steps below, I will demonstrate the how to create my dynamic measure which will change based on what is selected from the Disconnected table.

  • Below is my measure, with an explanation afterwards
    1. Selected Measure =
    2. VAR MySelection =
    3. SELECTEDVALUE ( ‘Measure Selection'[Measure Name], “Sales” )
    4. RETURN
    5. SWITCH (
    6. TRUE (),
    7. MySelection = “Sales”, [Sales],
    8. MySelection = “Costs”, [Costs],
    9. MySelection = “Margin”, [Margin],
    10. [Sales]
    11. )

  • Line 1, is the name of my measure called “Selected Measure”
  • Line 2, is where I am creating a variable called “My Selection” and this will store my DAX measure in the variable called “My Selection”
  • Line 3, I have used the SELECTEDVALUE DAX function, which will get the selected value from my disconnected table with the column called [Measure Name]
    • And if nothing is selected default to “Sales”
    • If I select “Costs” from the disconnected table, the Variable MySelection will hold the value of “Costs”
  • Line 4, once I have finished with my variables, you have to put in the RETURN to continue with the standard DAX functions.
  • Line 5, I am using the SWITCH DAX function, which will allow me to switch through different values, based on what I select.
  • Line 6, I use the TRUE() DAX function, which allows me to define multiple values to switch through.
  • Line 7, I am using the Variable from line 2 & 3, as my Starting Comparison, and then after the “=” is what value I am looking for, and in line 7 it is called “Sales”, which I then want to return the measure of [Sales]
  • Line 8, I am using the Variable from line 2 & 3, as my Starting Comparison, and then after the “=” is what value I am looking for, and in line 7 it is called “Costs”, which I then want to return the measure of [Costs]
  • Line 9, I am using the Variable from line 2 & 3, as my Starting Comparison, and then after the “=” is what value I am looking for, and in line 7 it is called “Margin”, which I then want to return the measure of [Margin]
  • And then for the SWITCH syntax if nothing is evaluated to true, then I default it to the measure of [Sales], which is also the default value if nothing is selected in the SELECTEDVALUE

Using the Dynamic Slicer to display different measures

Now finally I put it all together to show how when selecting a value from the slicer, it will then change to show the selected measure.

  • I created the Slicer using the values from my “Measure Selection” table
  • I then created 2 tables, one to show all the measures.
  • The second table has got the “Selected Measure” that I created earlier to show when I click on the Slicer it displays the correct measure. Which I can validate by comparing the 2 tables.

Conclusion

As you can see I have demonstrated how to use Disconnect tables and a slicer to show different measures in one table, which could also be used in a visualization, making it more dynamic and flexible for users consuming the reports.

You can download a copy of the PBIX file here: Multiple Disconnected Slicers.pbix