cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
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
Highlighted
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 46 members 1,032 guests
Please welcome our newest community members: