Slicing by Measure Categories in Power BI
Вставка
- Опубліковано 12 лип 2021
- 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 - Наука та технологія
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.
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.
This is amazing. I had given up slicing by measure and created a calculated column. Thanks a lot.
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!
Thank you! That’s a compliment that I really appreciate from a viewer.
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.
Can't believe this is so complicated, but thanks for doing this in such a succinct and understandable way. Cheers!
Great video and super well explained. This is the only option that works for me without killing the PBI database with countless server calls.
Was looking for this solution for weeks!!! Excellent video an very well explained!!!
thank you it works...It's quite simple in tableau, didn't expect to be this complicated in PBI.
I tried to find it during 3 days and finally I got it. Thank so much!!!
Good stuff bro. Helping me crush it at work !! Love the content
Very nice way of explaining the concept, makes this look so simple. Thanks for sharing. :)
This is such a simple yet powerful tip, thanks so much for sharing !
Of course, Paul!
I am brazilian, thank yo so much!! God Blessyou and your family
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?
Great! This is exactly what I have been searching for.
Thanks a ton ! Saved me loads of time and effort
This is exactly what I am looking for!!! thanks heaps!!! :)
Hi. This is really good thanks. I have one quick question though, I'm struggling to represent this in a Pie Chart, any tips?
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?
Simple yet very powerful!!! Thank you for sharing!
No problem, Raul!
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)?
Wow, just tip is right on time! Thank you for sharing.
Fantastic video. Thank you!
Also fits well with each monthly energy report
Well explained
excellent video, is there any way to do the same at a page level instead of visual level?
I found this very useful. Thanks!
Thank you so much for clarifying this concept!
No problem!
Great stuff man - Using this!
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?
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?
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.
This was awesome! Great content!
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?
How would this work if you wanted a multiple selection on a single filter? Could you pass this through the values function?
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.
I'd love to see that. No idea what this would take on the backend though...
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.
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
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!
Hey, I need the same thing but filter cards using this method
Thanks a lot I was looking for such amazing tutorial 🤗
No problem, Parul!
This is really useful. Thanks!
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.
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)?
How to do it on a card visual.. As here for table we can use filters but hwo about a card visual?
Great video!
Really like this method. Thanks for the vid.
No problem, Nate! Thanks for the watch.
Thanks. This was a life saver
Thank you so much! You are amazing!!!!!
Thanks a lot ! is it possible that it can be a multi select o it's strictly single select ?
Very informative. thank you so much.
Finally!! Great job...thank you!
No problem, Marc!
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?
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?
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.
This was great, thank you so much
Thanks! Helped a lot.
Amazing! Why It doesn't work with graph?
best explanation ever!
Is there another way to download the pbix file?
can we implement this in Power Pivot
When using a "Dropdown" slicer, how do you get it to show "all" records by default.
Very easy and alternative method 👌!!!
Glad to hear!!
How about multi select option? How would that work? I’ve only seen single select solutions. Tia!!
Thank you buddy!
It is awesome. I finished 3 of your courses from BI Elite site. I must say I miss your voice haha.
Hahahah I love it
Very helpful. Thankyou
No problem Sham!
Very helpful.
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!
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 && ....
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()
)
I wanted to know the same thing!
What’s the benefit of this method over adding a calculated column to determine the class + a slicer on the calculated column
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!
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?
Can you please provide me the data for this project
LIFE SAVER
Thanks!
THANK YOU!!!
I am sad I can’t do it with live connection 😢
Great trick 👏👏👏👍👌👌🥇
Simple but efficient 😁
Yes, indeed :)
Cool! Thank you)
No worries, Rus!
I believe 'ChicletSlicer' does the same thing, isn't it?
No, unfortunately. There's no out of the box way to slice on a measure. You have to use DAX!
@@BIElite Got it, thanks
neat
Thats interesting 🤔
Yes, I think so too 😁
A great solution, but I find your video quality is in need of improvement
Hi Butch, could you share any recommendations? I’m always trying to make the content better.
@@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.