Slicing by Measure Categories in Power BI

Подписаться 71 тыс.

Просмотров 56 тыс.

50% 1

In this Power BI tutorial, you'll learn how to filter based on a measure value! In this example, we have a measure that evaluates out to a few different options, which we can then slice by. This is not easy to do in Power BI out of the box, so you must use some clever visual-level filters to slice your visuals, but the end result is fantastic! See below for the PBIX download.
To enroll in my introductory or advanced Power BI courses:
training.bielite.com/
Blog Post/PBIX Download:
bielite.com/blog/filter-categ...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Connect with me on Twitter!
/ powerbielite

Наука

Опубликовано:

12 июл 2021

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 108

@exOr0808 3 месяца назад

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.

@tanyacraig2672 2 года назад

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.

@csrajaraman 3 года назад

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

@discrate 3 года назад

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 3 года назад

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

@TheBenLemonade 2 года назад

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

@faustomy 2 года назад

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

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

@paulwilding 3 года назад

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

@BIElite 3 года назад

Of course, Paul!

@xuhan9375 Год назад

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

@carn9977 3 года назад

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

@rafaeldeyta6863 Год назад

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

@jimitbhayani4127 3 года назад

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

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

@rauljimenez5485 3 года назад

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

@BIElite 3 года назад

No problem, Raul!

@vivekkp4628 Год назад

Thanks a ton ! Saved me loads of time and effort

@emmafeng55 2 года назад

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

@PallaviChauhan91 2 года назад

Thank you so much for clarifying this concept!

@BIElite 2 года назад

No problem!

@tak0331 3 года назад

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

@kuttapayis573 6 месяцев назад

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

@swebber2825 2 года назад

Fantastic video. Thank you!

@janekkaup2092 3 года назад

Also fits well with each monthly energy report Well explained

@jimmycarlin243 2 года назад

This was awesome! Great content!

@Parulmittal-wk3mp 3 года назад

Thanks a lot I was looking for such amazing tutorial 🤗

@BIElite 3 года назад

No problem, Parul!

@ksendhil Год назад

I found this very useful. Thanks!

@buckyberg 3 года назад

Finally!! Great job...thank you!

@BIElite 3 года назад

No problem, Marc!

@MrBodies07 Год назад

Great stuff man - Using this!

@VladMZ 3 года назад

This is really useful. Thanks!

@paulrockliffe2378 3 года назад

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 3 года назад

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

@VladMZ 3 года назад

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.

@sanjaysubair9268 2 года назад

Thanks. This was a life saver

@phonte2011 2 года назад

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

@mcnater 3 года назад

Really like this method. Thanks for the vid.

@BIElite 3 года назад

No problem, Nate! Thanks for the watch.

@mrdave6901 2 года назад

best explanation ever!

@ibrahimfares3086 2 года назад

Great video!

@flintshire5564 2 года назад

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.

@katarzynalaskowska4740 10 месяцев назад

Thanks! Helped a lot.

@nureyna629 2 года назад

Very informative. thank you so much.

@t4k3none 2 года назад

This was great, thank you so much

@SauravPTx Год назад

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

@Drdry8 3 года назад

Very easy and alternative method 👌!!!

@BIElite 3 года назад

Glad to hear!!

@simaocoimbra 2 года назад

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?

@ajitchaudhary4444 2 года назад

Very helpful.

@Badmintonistahajnal 3 года назад

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?

@shamf6624 3 года назад

Very helpful. Thankyou

@BIElite 3 года назад

No problem Sham!

@matiastaurizano299 2 года назад

Thank you buddy!

@erikacc5750 2 года назад

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)?

@mortenruus126 11 месяцев назад

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?

@BuiQuynhNhu 3 года назад

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

@BIElite 2 года назад

Hahahah I love it

@justinthobela1209 3 года назад

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

@dmitrikochubei3569 2 года назад

THANK YOU!!!

@conleyyang4318 3 года назад

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?

@danielsiffredi8403 2 года назад

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

@SanataniAryavrat 3 года назад

Great trick 👏👏👏👍👌👌🥇

@peterredgray2008 2 года назад

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.

@zuzanabocanski6580 10 месяцев назад

Thanks!

@jdlopez131 2 года назад

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

@falutaope2800 5 месяцев назад

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)?

@rusfinance1 3 года назад

Cool! Thank you)

@BIElite 3 года назад

No worries, Rus!

@alakmarshafin6326 2 года назад

LIFE SAVER

@mariapradeepm 3 года назад

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!

@muhammadyaseen8042 3 месяца назад

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?

@mehdihammadi6145 3 года назад

Simple but efficient 😁

@BIElite 3 года назад

Yes, indeed :)

@AlejandroCalderon2094 2 года назад

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

@Pooja-pd1ee 3 года назад

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.

@agustinrajan8621 Год назад

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

@aminegatou4492 2 года назад

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

@kundaninetshiongolwe454 4 месяца назад

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

@waldemarvogt66 6 месяцев назад

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?

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

@tinap.8387 6 дней назад

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 3 года назад

Amazing! Why It doesn't work with graph?

@tinaflemons263 2 года назад

Is there another way to download the pbix file?

@rajkumarrajan8059 Год назад

can we implement this in Power Pivot

@dsapraveen 3 года назад

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 2 года назад

I wanted to know the same thing!

@stuartsmith801 11 месяцев назад

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

@juanodonnell 3 месяца назад

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?

@mikej62 Год назад

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

@rossdan4098 3 года назад

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

@BIElite 3 года назад

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!

@AkshayKumar-oy9uo Год назад

Can you please provide me the data for this project

@emilymalkin270 4 месяца назад

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

@sahameed 3 года назад

Thats interesting 🤔

@BIElite 3 года назад

Yes, I think so too 😁

@simphiwe_stb 4 месяца назад

neat

@robidadurgaan 3 года назад

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

@BIElite 3 года назад

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

@robidadurgaan 3 года назад

@@BIElite Got it, thanks

@bbubarker 3 года назад

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

@BIElite 3 года назад

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

@bbubarker 3 года назад

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