cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
heytherejem Member
Member

Creating a slicer based on measures

I know this has been covered but I haven't found a suitable resolution that works so I'm giving this a go fearing the worst:

 

I have four measures which calculate four different totals e.g. Americas Volume, UK Volume, Singapore Volume, Middle East Volume.  The measures use different datasets to get their totals.

I want to have a slicer or chiclet slicer which people can select which of these measures they want to see in the visualisation, so I could have all four measures in a graph, (which wouldn't look great initially), then when the slicer is selected the graph only displays the measure that's been selected. 

 

I tried sticking all the measures in a bar graph, but that isn't filtering my other chart, and also it doesn't look good.

 

Any ideas? 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Creating a slicer based on measures

This can be done without too much trouble. 

 

1. Create a disconnected table with your the names you want to use as your Slicers. For example,create a table that has "Sales YTD" and "Sales LYTD"

 

2. Create a slicer using that table, being sure it's not related to any tables in the model

 

3. Create a measure that captures the value that is selected 

Selected Value=
SELECTEDVALUE( Table[Col] from #1, value if more than one values is selected)

https://docs.microsoft.com/en-us/dax/selectedvalue-function

 

4. Create another measure that will use the value in #3 in a SWITCH(TRUE() format)

  Show Selected:=
  SWITCH(
      TRUE(),
      [Selected Value]="Sales YTD",[SalesYTD],
      [Selected Value]="Sales LYTD",[SalesLYTD]
      what to show if more than 1 value used
  )

//selected value is the value of the measure in #3
//[Sales YTD] is just a place holder. This will be whatever measure you have

Put that in your matrix and should change based on the slicer selection from the inital table created.

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Creating a slicer based on measures

This can be done without too much trouble. 

 

1. Create a disconnected table with your the names you want to use as your Slicers. For example,create a table that has "Sales YTD" and "Sales LYTD"

 

2. Create a slicer using that table, being sure it's not related to any tables in the model

 

3. Create a measure that captures the value that is selected 

Selected Value=
SELECTEDVALUE( Table[Col] from #1, value if more than one values is selected)

https://docs.microsoft.com/en-us/dax/selectedvalue-function

 

4. Create another measure that will use the value in #3 in a SWITCH(TRUE() format)

  Show Selected:=
  SWITCH(
      TRUE(),
      [Selected Value]="Sales YTD",[SalesYTD],
      [Selected Value]="Sales LYTD",[SalesLYTD]
      what to show if more than 1 value used
  )

//selected value is the value of the measure in #3
//[Sales YTD] is just a place holder. This will be whatever measure you have

Put that in your matrix and should change based on the slicer selection from the inital table created.

View solution in original post

heytherejem Member
Member

Re: Creating a slicer based on measures

That totally worked! Thank you @Nick_M  you're a genius! What a great solution. I love that it's blank until you select as well. Perfect. 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)