I had previously blogged Power BI – Using a Slicer to show different measures
where I showed the steps on to have a different measure based on a slicer selection. I got a few comments from that blog post, where people were asking if it was possible to have multiple Slicers which could be used.

If you are reading this blog post, I would recommend reading my previous blog post Power BI – Using a Slicer to show different measures, this is a follow-on blog post.

Example

In this working example I am going to add in an additional disconnected slicer for Currency.

This will allow me to click on my existing measure, which is for Sales or Cost, and have the ability to then show it in different currencies.

Below is what my Fact table looks like.

As shown above I have got my different columns for my sales and costs, as well as the other values in the different currencies.

Creating the 2nd Disconnected Table

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 Currency
  • 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 Currency column, then in the Ribbon I went to Modeling and then clicked on the Sort By Column and selected Order

Creating the Base Measures

I like to call these measures base measures, because this is the base measure which I am going to build my additional measures on top of these base measures.

Currently have got two values for my Measure Table which are “Sales” and “Costs”

I then need to create 2 measures one for “Sales” and one for “Costs” as shown below.

Sales =
VAR SelectedCurrency =
    SELECTEDVALUE ( 'Currency'[Currency], "AUD" )
RETURN
    SWITCH (
        TRUE (),
        SelectedCurrency = "AUD", SUM ( Sales[Sales Amount] ),
        SelectedCurrency = "USD", SUM ( Sales[Sales Amount USD] ),
        SelectedCurrency = "EUR", SUM ( Sales[Sales Amount EUR] ),
        0
    )

  • I am using the same pattern as I did in the previous blog post.
  • Lines 2 – 3
    • Here I am getting the value that has been selected on the Currency Slicer (Which I will create in a future step below)
  • Lines 4 – 11
    • I am then taking my variable “SelectedCurrency” and where it matches, then using the related measure

I now do a very similar thing with the Costs, and this time I am using the Cost Amount. The same pattern is used so please refer to the details in the step above.

Costs =
VAR SelectedCurrency =
    SELECTEDVALUE ( 'Currency'[Currency], "AUD" )
RETURN
    SWITCH (
        TRUE (),
        SelectedCurrency = "AUD", SUM ( Sales[Cost Amount] ),
        SelectedCurrency = "USD", SUM ( Sales[Cost Amount USD] ),
        SelectedCurrency = "EUR", SUM ( Sales[Cost Amount EUR] ),
        0
    )


The final measure that I now need to create is based on what is selected in the Measure slicer. This will allow me to then dynamically change between “Sales” or “Costs

Dynamic Measure = 
VAR MySelection =
    SELECTEDVALUE ( 'Measure'[Measure Name], "Sales" )
RETURN
    SWITCH (
        TRUE (),
        MySelection = "Sales", [Sales],
        MySelection = "Costs", [Costs],
        [Sales]
    )

  • Once again, I am using the same pattern as above.
  • Lines 2 – 3
    • I am getting the value from my Measure Slicer
  • Line 7
    • If the selection = “Sales” then I am going to use the [Sales] measure which I created earlier
  • Line 8
    • If the selection = “Costs” then I am going to use the [Costs] measure which I created earlier

Multiple Disconnected Slicers in Action

Below you can see how this works with multiple disconnected slicers.

I have put below the entire table, which will enable you to see that the Dynamic measure is working as expected.

GIF HERE

 1

2

3

4

5

6

7

8

9

10

11

12 

13

14

15

And finally, here is a link to the PBIX Multiple Disconnected Slicers
if you are looking for more details

Conclusion

I do hope that you have found this useful and if you do have any questions or comments, I encourage you to please leave them in the section below.

Thanks for reading!