Forum Discussion
Multiple Measures Slicer
Hi all,
How can I create a filter which switches between the different measures in the below graph?
I want to be able to select mutiple measures at once.
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".
13 Replies
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".
Hey, I love the solution! It worked great for me with one kink...how can I hide the measures not being selected from the visual? (highlighted below)
I'm using this method in a matrix format, and when I unselect the measure it hides the data from showing but the row label still exists (actual company numbers covered here)
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
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.
The method I posted above works with mutliple selections
You can try a different approach.
create a measure along the lines of:matrix measure =
SWITCH (SELECTEDVALUE (Data field[column]),
"% under 20", FORMAT([% under 20 measure], "Percentage",
"Calls abandoned", FORMAT([Calls abandoned measure], "#,###",
....
)
(you need the FORMAT function for each measure to return the correct format for each measure)
Then create the matrix using the field from the Data Field table, add the [matrix measure] and the slicer from the Data field table
HenryJS Probably need to use the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick/ba-p/279563
Not sure I 100% follow what you are trying to do however. Sample source data would be helpful along with maybe your measure formulas.
HenryJS , You need to create a table with measure name and then use that in the slicer and select a measure and use it
refer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115you can check out this tutorial too 🙂
