Slicing by Measure Categories in Power BI

[@discrate]

Your pbi videos are very well explained. As fast as needed and efficient. I really appreciate the work you invest in your tutorials. Keep up the good work Parker!

[@BIElite]

Thank you! That’s a compliment that I really appreciate from a viewer.

[@zuzanabocanski6580]

Thanks!

[@exOr0808]

This solution has helped me many times over the years, thank you for your video. Only one small suggestion I have is to use 'Blank()' as opposed to '0' in the Switch statement. Otherwise Power BI is forced to show all possible records and not just the ones which have data.

[@csrajaraman]

This is amazing. I had given up slicing by measure and created a calculated column. Thanks a lot.

[@hoongary]

Brilliant, thank you! I've been looking for this solution for 2 days now, I've hit a dead end with dynamic segmentation and spent a lot of time there (which was also useful but not for my scenario), when I needed this solution all along.

[@TheBenLemonade]

Can't believe this is so complicated, but thanks for doing this in such a succinct and understandable way. Cheers!

[@flintshire5564]

Thank you so much, that's really brilliant! I'm just wondering what if it's a multi-selection scenario for the slicer, how should we modify the code for Filter Status?

[@ignaciogargiulo7519]

You can change the SELECTEDVALUE() for ALLSELECTED(). But then, instead of doing CurrentSelection = "Increased Sales" && .... , you should do "Increased Sales" IN CurrentSelection && .... That should work.

[@medha68]

@@ignaciogargiulo7519 - I tried to use allselected and IN condition, it gives me an error - A table of multiple values was supplied where a single value was expected.

[@nmprofessional]

Great video and super well explained. This is the only option that works for me without killing the PBI database with countless server calls.

[@rafaeldeyta6863]

I tried to find it during 3 days and finally I got it. Thank so much!!!

[@paulrockliffe2378]

This is a great fix, a problem I'd given up trying to solve, so I'm sure it'll get a lot of use, thanks! It is the sort of thing Microsoft should be designing into Power BI though.

[@BIElite]

I'd love to see that. No idea what this would take on the backend though...

[@VladMZ]

Power BI is missing a number of simple features that prevents them from taking over the world. For example, in government and enterprise accounting it is important to be able to have more than one hierarchical columns (expandable/collapsible), because there are hierarchies in almost every segment of chart of accounts. Because Power BI's matrix visual doesn't allow for that, the tool is useless in a lot of very lucrative clients.

[@xuhan9375]

thank you it works...It's quite simple in tableau, didn't expect to be this complicated in PBI.

[@tanyacraig2672]

This is brilliant, thank you so much. I've been battling with slicing by a measure very similar to this for hours ! Really clearly explained and loved the step of adding the filter column in to show how it worked.

[@paulwilding]

This is such a simple yet powerful tip, thanks so much for sharing !

[@BIElite]

Of course, Paul!

[@BuiQuynhNhu]

It is awesome. I finished 3 of your courses from BI Elite site. I must say I miss your voice haha.

[@BIElite]

Hahahah I love it

[@carn9977]

Good stuff bro. Helping me crush it at work !! Love the content

[@faustomy]

Was looking for this solution for weeks!!! Excellent video an very well explained!!!

[@kundaninetshiongolwe454]

Thanks a lot ! is it possible that it can be a multi select o it's strictly single select ?

[@zamvorga]

It is truly helpful explanation, clear, and easy to understand . Thanks!

[@AlejandroCalderon2094]

How would this work if you wanted a multiple selection on a single filter? Could you pass this through the values function?

[@rauljimenez5485]

Simple yet very powerful!!! Thank you for sharing!

[@BIElite]

No problem, Raul!

[@yiyiwang8861]

This is amazing and brilliant. I need to have multi selections for my filter, but this trick doesn't work for multiselection. How can I adjust to adapt to multiselection? Thank you in advance!

[@ignaciogargiulo7519]

In the Filter Status Measure, you can change the SELECTEDVALUE() function for ALLSELECTED(), but then that would return a number of rows >1. So you need also to change the logic of the SWITCH function. Instead of doing CurrentSelection = "Increased Sales " && ..... you should do: "Increased Selection" IN CurrentSelection && ....

[@phonte2011]

I am brazilian, thank yo so much!! God Blessyou and your family

[@PallaviChauhan91]

Thank you so much for clarifying this concept!

[@BIElite]

No problem!

[@mortenruus126]

Very well explained. I used this trick in a Supplier Dashboard to categorize my suppliers by A, B, C and D. Will the filtering also work on card visual e.g. if you want filter 'Decreased Sales' and show show '2021 sales' in the visual card?

[@agustinrajan8621]

How to do it on a card visual.. As here for table we can use filters but hwo about a card visual?

[@vivekkp4628]

Thanks a ton ! Saved me loads of time and effort

[@falutaope2800]

Great video and thanks for the tutorial. I currently stuck with something similar. Can I use a categorical measure to filter a numeric axis (say the y-axis of a bar chart)?

[@muhammadyaseen8042]

This video helps a lot. I have a quite similar scenario but on selection of filter, Totals in data table are missing. Can somebody help in this?

[@jdlopez131]

Is it possible to create a table of those filters you created to sum sales by those groups? In other words, sum sales and break it down by the groups from the measure

[@kuttapayis573]

Great! This is exactly what I have been searching for.

[@buckyberg]

Finally!! Great job...thank you!

[@BIElite]

No problem, Marc!

[@simaocoimbra]

Excellent video and very well explained. By any chance there is a way to add the "measure_filter" to the panel to filter all pages?

[@janekkaup2092]

Also fits well with each monthly energy report Well explained

[@waldemarvogt66]

Hello, this solution is brilliant, thank you! However I have one issue, I'm not able to drop my Filter Status to Filters tab. Any advice what you be a reason for that?

[@JiffyDealer]

Awesome! Just what I needed!!

[@erikacc5750]

very enlightening!!! :) .. question ... is there a way to propagate the selection to other visuals in the same page which uses other linked queries through i.e. the customer filed ? or make the filter status=1 to all the page(s)?

[@conleyyang4318]

thanks for the video!! some follow-up questions. Is it possible to have this measure in the visual but without 2020 Sales and 2021 Sales. for example, I just want to see for each customer which sales category they fall in and how many orders each customer has. And on top of that, can I use that calculated measure in the first column and use it to count how many customers each category has by counting the customer id?

[@furman505]

I'm trying to do the same for bar chart, where Y is defined my SUM measures and X by dates. Each month on the chart has 3 bars - Sales of current year, plan of current sales and how sales in the month looked in previous year. Do you have some idea how to filter bars with slicers? I need to show every 3 bars, sometimes 2 of them and sometimes only one.

[@danielsiffredi8403]

excellent video, is there any way to do the same at a page level instead of visual level?

[@ibrahimfares3086]

Great video!

[@Parulmittal-wk3mp]

Thanks a lot I was looking for such amazing tutorial 🤗

[@BIElite]

No problem, Parul!

[@jimitbhayani4127]

Very nice way of explaining the concept, makes this look so simple. Thanks for sharing. :)

[@mcnater]

Really like this method. Thanks for the vid.

[@BIElite]

No problem, Nate! Thanks for the watch.

[@peterredgray2008]

Thanks for the video, how possible is it to do this for a measure that is generated by other measures? For example I have a measure (called Error) that returns a string based on the values of other measures that have different rules to identify errors. I want to be able to slice on the string values that is outputted from the Error measure. Following this tutorial it does not work for my scenario.

[@rajkumarrajan8059]

can we implement this in Power Pivot

[@mariapradeepm]

Thanks for the video, I have one quick doubt! Why can't we create a relationship between 2 tables, So automatically filter will happen instead of new measure, Rite? Please let me know!

[@Pooja-pd1ee]

So I have around 50+ measures created in the report and would want to group them in some way and use that group to select the particular set of measures. Is there a way that could be achieved? Unpivoting isn't an option as all calculations are getting evaluated at report level.

[@MrBodies07]

Great stuff man - Using this!

[@mrdave6901]

best explanation ever!

[@Badmintonistahajnal]

Great video! Learned a lot by doing the steps with you. In my case I wanted to give the "Sales class" measure to a map visual (I have the coordinates) in the legend section (basically to color the customers which had Incresed sales, decreased sales and so on) but the map visual is not letting me do it ;(. Do you know the why maybe and how to fix it?

[@mikej62]

How about multi select option? How would that work? I’ve only seen single select solutions. Tia!!

[@justinthobela1209]

Hi. This is really good thanks. I have one quick question though, I'm struggling to represent this in a Pie Chart, any tips?

[@Drdry8]

Very easy and alternative method 👌!!!

[@BIElite]

Glad to hear!!

[@ksendhil]

I found this very useful. Thanks!

[@aminegatou4492]

Hey, I need the same thing but filter cards using this method

[@stuartsmith801]

When using a "Dropdown" slicer, how do you get it to show "all" records by default.

[@emmafeng55]

This is exactly what I am looking for!!! thanks heaps!!! :)

[@jimmycarlin243]

This was awesome! Great content!

[@SauravPTx]

Thank you so much! You are amazing!!!!!

[@tinaflemons263]

Is there another way to download the pbix file?

[@juanodonnell]

wouldn't it be simpler/easier if in the switch statement you start with all customers,1 and then generalize with currentSelection=currentClass,1 and have the same functionality? the order of the switch conditions is important?

[@rossdan4098]

What’s the benefit of this method over adding a calculated column to determine the class + a slicer on the calculated column

[@BIElite]

That wouldn’t be dynamic based on other slicer selections or cross filtering! I was hoping to explain this properly in the intro section. Hope this helps!

[@dsapraveen]

Thank You for sharing an amazing tip, I have a small query, in SWITCH case instead of multiple compare statements, can we just write below statement instead: SWITCH( TRUE(), Selection = CurrentStatus, 1, Selection = "All Customers", 1, BLANK() )

[@Mrglasshalfempty]

I wanted to know the same thing!

[@swebber2825]

Fantastic video. Thank you!

[@_RickySetiawan]

TYSM. You help me a lot

[@tinap.8387]

Omggggggggg this is the best ever , i exactly what i want to in my report.this video really help me a lot 🎉🎉🎉🎉 thx so muchhhhhhhhhhhhh

[@giorgioguirreri1800]

Amazing! Why It doesn't work with graph?

[@shamf6624]

Very helpful. Thankyou

[@BIElite]

No problem Sham!

[@tak0331]

Wow, just tip is right on time! Thank you for sharing.

[@sanjaysubair9268]

Thanks. This was a life saver

[@AkshayKumar-oy9uo]

Can you please provide me the data for this project

[@mehdihammadi6145]

Simple but efficient 😁

[@BIElite]

Yes, indeed :)

[@katarzynalaskowska4740]

Thanks! Helped a lot.

[@VladMZ]

This is really useful. Thanks!

[@matiastaurizano299]

Thank you buddy!

[@t4k3none]

This was great, thank you so much

[@nureyna629]

Very informative. thank you so much.

[@rusfinance1]

Cool! Thank you)

[@BIElite]

No worries, Rus!

[@emilymalkin270]

I am sad I can’t do it with live connection 😢

[@alakmarshafin6326]

LIFE SAVER

[@robidadurgaan]

I believe 'ChicletSlicer' does the same thing, isn't it?

[@BIElite]

No, unfortunately. There's no out of the box way to slice on a measure. You have to use DAX!

[@robidadurgaan]

@@BIElite Got it, thanks

[@ajitchaudhary4444]

Very helpful.

[@SanataniAryavrat]

Great trick 👏👏👏👍👌👌🥇

[@dmitrikochubei3569]

THANK YOU!!!

[@sahameed]

Thats interesting 🤔

[@BIElite]

Yes, I think so too 😁

[@simphiwe_stb]

neat

[@bbubarker]

A great solution, but I find your video quality is in need of improvement

[@BIElite]

Hi Butch, could you share any recommendations? I’m always trying to make the content better.

[@bbubarker]

@@BIElite Not really as I don't know what causes it but your videos are very blurry to me I have a very high resolution monitor, perhaps you need to increase the resolution you are recording with.