Power BI – Using a Slicer to show different measures
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.
- 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.
- 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
- 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.
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: Disconnected Slicers.pbix