Forum Discussion
Multiple Measures Slicer
Hi HenryJS ,
You may create a table 'SlicerTable' via the button "Enter Data", type all of the measures' names in one column named "MeasureName" one by one, and put this column [MeasureName] into slicer to filter data.
Then you may create a measure like DAX below, put it into Values box of chart visual.
SelectedMeasure= SWITCH( SELECTEDVALUE('SlicerTable'[MeasureName]), "Candidate Calls" ,[Candidate Calls], "Client Calls" ,[Client Calls], … )Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
As has been suggested, you need a disconnected table listing the measure names. To make the "slicer" multiselectable (is that a word?), you need to create new measures for each of the measures listed.
For the following example I'm using a simple dataset and the idea is to display a selectable combination of 4 measures.
1) first create the table using the "Enter Data" option in the Home tab (I've called the table 'Select Measure'):
2) Create a measure to check if a filter has been applied:
Countrows Select Measure = IF(ISFILTERED('Select Measure'[Selected Measure]), COUNTROWS(VALUES('Select Measure'[Selected Measure])), BLANK())You can also use this measure to create conditional title/text meassage as a warning to users to make a selection:
Warning = IF(ISBLANK([Countrows Select Measure]), "Please Select at Least One Measure!!")3) Create a new measure for each measure you wish to display which is referenced to the slicer table following the code:
Sales 2018 (sel) = VAR calc = COUNTROWS( FILTER('Select Measure', 'Select Measure'[Selected Measure]= "Sales 2018")) RETURN IF(ISBLANK([Countrows Select Measure]), BLANK() , IF(calc= 1, [Sales 2018], BLANK()))You can then build your report page with the slicer, and the line chart visual using these new measures as your "Values".
As has been suggested, you need a disconnected table listing the measure names. To make the "slicer" multiselectable (is that a word?), you need to create new measures for each of the measures listed.
For the following example I'm using a simple dataset and the idea is to display a selectable combination of 4 measures.
1) first create the table using the "Enter Data" option in the Home tab (I've called the table 'Select Measure'):
2) Create a measure to check if a filter has been applied:
Countrows Select Measure =
IF(ISFILTERED('Select Measure'[Selected Measure]),
COUNTROWS(VALUES('Select Measure'[Selected Measure])),
BLANK())
You can also use this measure to create conditional title/text meassage as a warning to users to make a selection:
Warning = IF(ISBLANK([Countrows Select Measure]), "Please Select at Least One Measure!!")
3) Create a new measure for each measure you wish to display which is referenced to the slicer table following the code:
Sales 2018 (sel) =
VAR calc = COUNTROWS(
FILTER('Select Measure',
'Select Measure'[Selected Measure]= "Sales 2018"))
RETURN
IF(ISBLANK([Countrows Select Measure]),
BLANK() ,
IF(calc= 1, [Sales 2018],
BLANK()))
You can then build your report page with the slicer, and the line chart visual using these new measures as your "Values".
Hi PaulDBrown,
I am trying to implement your solution but keep running into the below error. Can I please ask for your insights into what might be causing this?
There is currently actually an easier way to achieve this since the intorduction to field parameters in last month's PBI Desktop update.
Select the option for "New parameter" under Modeling in the ribbon. Choose "Fields" and in the interface, choose a name, add the measures you need to the "Add and reorder fields" box and leave the "Add slicer to this page" option checked:
A new table will be added and a slicer will appear on the report page. Now create the visual and add the "Measure Selector" field to the Y-axis to get:
I've attached the sample PBIX file
Thanks so much, this is exactly what I needed!
PaulDBrown Thank you so much for this clean solution!
Is there anyway that two measures are shown when selecting a unique measure? I would like to show both the branch data and the whole company data when selecting "Sales", for example. I have a measure for "Branch Sales" and another measure for "Company Sales" but I only want to show "Branch Sales" in the slicer to avoid having too many fields in the slicer.
Please let me know if you need further details.
Thank you!
