Forum Discussion
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?
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 havePut that in your matrix and should change based on the slicer selection from the inital table created.
2 Replies
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 havePut that in your matrix and should change based on the slicer selection from the inital table created.