Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HenryJS
Post Prodigy
Post Prodigy

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.

 

 

 

Capture.PNG

2 ACCEPTED SOLUTIONS
v-xicai
Community Support
Community Support

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.

View solution in original post

PaulDBrown
Community Champion
Community Champion

@HenryJS 

 

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'):

Create table.JPG

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".
Line Chart.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

13 REPLIES 13
theov
Advocate II
Advocate II

you can check out this tutorial too 🙂

https://www.youtube.com/watch?v=KYcKmVQNaC8

PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PaulDBrown
Community Champion
Community Champion

@HenryJS 

 

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'):

Create table.JPG

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".
Line Chart.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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?Screen Shot 2022-07-22 at 4.33.10 PM.png

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:

measure selector.png

 

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:

field bucket.pngMeasureSelector.gif

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






@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!

Thanks so much, this is exactly what I needed!

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)

Sethj33_0-1633714470750.png

 

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)

Sethj33_1-1633714781585.png

 

 

v-xicai
Community Support
Community Support

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.

@v-xicai this works however when I make multiple selections on the filter, it shows no data in the graph. Picture below.

Is there a way to be able to select the required measures and show them in the visual?

Thanks

Capture.PNGCapture1.PNG

@HenryJS

The method I posted above works with mutliple selections





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User
Super User
Greg_Deckler
Super User
Super User

@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...

 

Not sure I 100% follow what you are trying to do however. Sample source data would be helpful along with maybe your measure formulas.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors