No video
Slicing by Measure Categories in Power BI
Рет қаралды 56,190
Күн бұрын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.bieli...
Blog Post/PBIX Download:
bielite.com/bl...
Elite Power BI Consulting:
bielite.com/
Data Insights Tools:
www.impktful.com/
Connect with me on Twitter!
/ powerbielite
@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.
@zamvorga 3 күн бұрын
It is truly helpful explanation, clear, and easy to understand . Thanks!
@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.
@csrajaraman 3 жыл бұрын
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 2 жыл бұрын
Can't believe this is so complicated, but thanks for doing this in such a succinct and understandable way. Cheers!
@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 2 жыл бұрын
I tried to find it during 3 days and finally I got it. Thank so much!!!
@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.
@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.
@carn9977 3 жыл бұрын
Good stuff bro. Helping me crush it at work !! Love the content
@paulwilding 3 жыл бұрын
This is such a simple yet powerful tip, thanks so much for sharing !
@BIElite 3 жыл бұрын
Of course, Paul!
@kuttapayis573 6 ай бұрын
Great! This is exactly what I have been searching for.
@xuhan9375 Жыл бұрын
thank you it works...It's quite simple in tableau, didn't expect to be this complicated in PBI.
@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.
@phonte2011 2 жыл бұрын
I am brazilian, thank yo so much!! God Blessyou and your family
@vivekkp4628 Жыл бұрын
Thanks a ton ! Saved me loads of time and effort
@faustomy 2 жыл бұрын
Was looking for this solution for weeks!!! Excellent video an very well explained!!!
@PallaviChauhan91 2 жыл бұрын
Thank you so much for clarifying this concept!
@BIElite 2 жыл бұрын
No problem!
@rauljimenez5485 3 жыл бұрын
Simple yet very powerful!!! Thank you for sharing!
@BIElite 3 жыл бұрын
No problem, Raul!
@janekkaup2092 3 жыл бұрын
Also fits well with each monthly energy report Well explained
@BuiQuynhNhu 3 жыл бұрын
It is awesome. I finished 3 of your courses from BI Elite site. I must say I miss your voice haha.
@BIElite 3 жыл бұрын
Hahahah I love it
@mcnater 3 жыл бұрын
Really like this method. Thanks for the vid.
@BIElite 3 жыл бұрын
No problem, Nate! Thanks for the watch.
@mrdave6901 2 жыл бұрын
best explanation ever!
@buckyberg 3 жыл бұрын
Finally!! Great job...thank you!
@BIElite 3 жыл бұрын
No problem, Marc!
@muhammadyaseen8042 4 ай бұрын
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?
@SauravPTx Жыл бұрын
Thank you so much! You are amazing!!!!!
@Parulmittal-wk3mp 3 жыл бұрын
Thanks a lot I was looking for such amazing tutorial 🤗
@BIElite 3 жыл бұрын
No problem, Parul!
@agustinrajan8621 Жыл бұрын
How to do it on a card visual.. As here for table we can use filters but hwo about a card visual?
@ksendhil Жыл бұрын
I found this very useful. Thanks!
@jimitbhayani4127 3 жыл бұрын
Very nice way of explaining the concept, makes this look so simple. Thanks for sharing. :)
@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)?
@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?
@matiastaurizano299 2 жыл бұрын
Thank you buddy!
@Drdry8 3 жыл бұрын
Very easy and alternative method 👌!!!
@BIElite 3 жыл бұрын
Glad to hear!!
@kundaninetshiongolwe454 5 ай бұрын
Thanks a lot ! is it possible that it can be a multi select o it's strictly single select ?
@alakmarshafin6326 2 жыл бұрын
LIFE SAVER
@emmafeng55 2 жыл бұрын
This is exactly what I am looking for!!! thanks heaps!!! :)
@tinap.8387 27 күн бұрын
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
@MrBodies07 Жыл бұрын
Great stuff man - Using this!
@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?
@tak0331 3 жыл бұрын
Wow, just tip is right on time! Thank you for sharing.
@zuzanabocanski6580 10 ай бұрын
Thanks!
@sanjaysubair9268 2 жыл бұрын
Thanks. This was a life saver
@stuartsmith801 Жыл бұрын
When using a "Dropdown" slicer, how do you get it to show "all" records by default.
@rajkumarrajan8059 Жыл бұрын
can we implement this in Power Pivot
@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?
@AlejandroCalderon2094 2 жыл бұрын
How would this work if you wanted a multiple selection on a single filter? Could you pass this through the values function?
@juanodonnell 4 ай бұрын
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?
@shamf6624 3 жыл бұрын
Very helpful. Thankyou
@BIElite 3 жыл бұрын
No problem Sham!
@jimmycarlin243 2 жыл бұрын
This was awesome! Great content!
@ibrahimfares3086 2 жыл бұрын
Great video!
@giorgioguirreri1800 3 жыл бұрын
Amazing! Why It doesn't work with graph?
@swebber2825 2 жыл бұрын
Fantastic video. Thank you!
@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!
@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.
@katarzynalaskowska4740 11 ай бұрын
Thanks! Helped a lot.
@mehdihammadi6145 3 жыл бұрын
Simple but efficient 😁
@BIElite 3 жыл бұрын
Yes, indeed :)
@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?
@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)?
@mikej62 Жыл бұрын
How about multi select option? How would that work? I’ve only seen single select solutions. Tia!!
@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?
@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.
@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
@VladMZ 3 жыл бұрын
This is really useful. Thanks!
@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?
@aminegatou4492 2 жыл бұрын
Hey, I need the same thing but filter cards using this method
@danielsiffredi8403 2 жыл бұрын
excellent video, is there any way to do the same at a page level instead of visual level?
@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 && ....
@ajitchaudhary4444 2 жыл бұрын
Very helpful.
@t4k3none 2 жыл бұрын
This was great, thank you so much
@nureyna629 2 жыл бұрын
Very informative. thank you so much.
@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!
@emilymalkin270 4 ай бұрын
I am sad I can’t do it with live connection 😢
@tinaflemons263 2 жыл бұрын
Is there another way to download the pbix file?
@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
@rusfinance1 3 жыл бұрын
Cool! Thank you)
@BIElite 3 жыл бұрын
No worries, Rus!
@dmitrikochubei3569 2 жыл бұрын
THANK YOU!!!
@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
@SanataniAryavrat 3 жыл бұрын
Great trick 👏👏👏👍👌👌🥇
@simphiwe_stb 5 ай бұрын
neat
@sahameed 3 жыл бұрын
Thats interesting 🤔
@BIElite 3 жыл бұрын
Yes, I think so too 😁
@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.
Adam Finer - Learn BI Online
Рет қаралды 97 М.