Using OR conditions between slicers in DAX
Vložit
- čas přidán 30. 07. 2024
- How to implement a logical OR condition between two slicers of a Power BI report, or of a PivotTable in Excel.
By default, when relying on more than one slicer they are considered in an AND condition. This video+article explains how to change this behavior using DAX.
Article and sample download: sql.bi/66373?aff=yt
How to learn DAX: www.sqlbi.com/guides/dax/?aff=yt
The definitive guide to DAX: www.sqlbi.com/books/the-defin... - Věda a technologie
Within 20 minutes I learned more than the last 6 months! This is a real eyeopener. Great, Thank you very much.
Wow, such a great video Alberto, I really like your way of teaching by starting with a first version of the measure that most of the people can come up with then improve and explaining what are the drawbacks of each technique.
Thank you!
Absolutely amazing. I don't even know how to thank you, this will help me a lot in my current power bi project.
Amazing ! Brilliant demonstration of different ways to calculate the same thing ... but more importantly how to think when writing DAX !
Great video! I love how you show the different solutions - it really makes me think about functions I don’t know as well
I've expended a lot of hours trying make OR working on my measures, it works, but with a lot of limitations :) Thank's, thank's a lot!
Amazing! Cannot stop following your videos and courses! Thanks a million!
Loved Set approach and it can only come from sql background.
Thank you so much for the clarity.
You're awesome!
Fabulous! Thank you for make us see this options so clearly!
Thank you Sir!
I am happy that i learn DAX directly from the Grand Master of DAX.
🎉
Thanks this was really informative and just shows that you can obtain the same result in many different ways. There is not right or wrong, just different
Excellent explanations Alberto. You're a genius.
Fantastic way of explaining. Great job! Thanks for sharing!
Amazing! You just woke up a detective inside, inspiring to try more and more. I wish I had a teacher like you in school ;)
Wow!!! Such a nice approach for learning DAX
👏👏👏
Excellent explanation, I liked seeing the different ways to solve a problem, too often a single solution is presented and it doesn't examine alternatives.
Excellent lesson. Thank you Master!
super. what an excellent video with various ways to achieve the goal. I loved it. SOOOOOO helpful practically. I have more options now to implement if client requires.
You deserved more likes and viewers man. Ty for the lesson.
I need to give this guy a hug!
Thx for detailed explanation. Appreciated.
Hi Alberto Ferrari
You are amazing.
I like your way of learning and content deliverable and really really amazing.
Very big thanks to share with your useful deliverable.
i used your all logic in my day to day work.
your blank row logic is very useful and add-columns+Generate+Row
You have always wonderful DAX Code :)
Thanks for your great videos
Excellent video! As to which one is best? My preference is always to start with simplest to understand/maintain, only using more complex or otherwise sophisticated queries if warrented by performance.
such a great explanation! Thank you very much :) I have some questions about it, I have to implement a dashboard that contains the distinct number of customers with 27 slicers (categorical and continuous) and I want to add an AND/OR slicers in it. What would be the optimal way to do it? Thank you very much in advance :)
Amazing, thx a lot!
Great and amazing, Thanks a lot
I'm always amazed how much thing you can do in DAX, how can you obtain so many interesting result with it. Loved the video, we can learn a lot from it.
+1 04:29 - ...You.Are.Missing.A.Space.There!
Great!!!..Sir..
This way is not working for 3 different slicers, Is there any other way to use OR clause with 3 different date filters/slicers
Excellent demonstration, very well explained, even though I am Latino
Thanks for a very interesting and useful tutorial, it helped me in my work, but the problem I'm having is that when "All" is selected in the slicer, I get an empty table😒
What i'm missing?
Is there any hierarchical table, eg if I select the group head name the field of table name should be replaced with FH name and if I select FH name it should be replaced with CH name but the name should be replaced in same table
I appreciate all your video and the training course which I subscribed to
I have a couple of questions on this contents while I follow your measures.
1. I got a wrong results when I use a measure of [Units] intead of a column of 'Sales[Quantity]. It would be great if anybody could answer this.
SUMX(******** ,[Units]) versus SUMX(******** ,Sales[Quantity])
Great. How do combine so you can choose and or over several slicers
Good stuff, but I was hoping for discussion of the big limitation: these only work specifically for Brand and Occupation. Is it possible to construct a "universal OR" measure? Where *whatever* slicers the report-developer puts onto the canvas (i.e. not hard coded ahead of time), the measure returns the OR value for the slicer selections?
No, unfortunately it is not possible to create code that works with any slicer placed in the report.
Awesome
Great channel, great video. The crossjoin solution naturally does not seem a good idea to me as it is normally a very "heavy" calculation is it not?
Also I thought I have seen a video where you dive into the difference using ALL as a table function vs using it as a modifier with or without arguments in calculate? Could you point me to that?
Best regards
The crossjoin could be expensive, it depends on the number of unique values you have in the two slicers. However, you might be surprised about the performance, which is quite good even though you have a few thousands of items in one slicer and only a couple of items selected in the other. I suggest you try and benchmark it.
You probably want the videos in this playlist: czcams.com/play/PLU6II7MW-aiIees6mrPfdjt9c8noi7P66.html
@@SQLBI Thanks a lot appreciate it!
Are duplicate values as a result of the UNION in example OR #5 kept ? If yes, why does this formula show the correct results?
Supercool!
I think it can be simpler. Have taken a simple population table. Happy to have input/suggetsions on same.
OR = var state = SELECTEDVALUE(Pouplation[States])
var country = SELECTEDVALUE(Pouplation[Country])
return CALCULATE(sum(Pouplation[Population]),Pouplation[Country] = country || Pouplation[States] = state)
do you think its allow Country=India || State="Alaska'? i mean does it show two rows? its not working in My case. both State and Country from Same table in my case
Hello, thank you for your very good and strong tutorials ... it is possible to put the database that you use for training for download so that we can use it according to your training .... Thank you very much.
You can download the sample files in related article linked in the description.
If you want the underlying SQL database, you can find it in the companion content of our book you can download here: www.sqlbi.com/books/the-definitive-guide-to-dax-2nd-edition/ (you don't have to but the book to download the companion content).
@@SQLBI Thank you very much
@@SQLBI Thank you very much.
is it possible for two columns of same table.
Cool 😎
The Union will create some duplicates right? It does not appear to affect the calculations though. I am curious as to why that is the case.
Also, I am not sure how the filtering worked in the SUMMARIZE version of the measure. How does the table that got created on the fly, retain its relationships to the other tables in the model?
Duplicated values don't affect the filter context.
SUMMARIZE doesn't lose data lineage in the "groupby" columns!
@@SQLBI Thank you for taking the time to respond to my queries.
Here is a link to a post I wrote back in 2014 to get OR Filtering in Excel. I went with the CROSSPRODUCT approach:
strugglingtoexcel.com/2014/02/21/permutations-lists-excel/
@@SQLBI Lovely video.
It is very apparent that you pour in a lot of effort into coming up with illustrations that showcase exactly how things work with no room for doubt. Very thankful for making these videos free to watch. God Bless!
Could the code be optimized further by using SELECTEDVALUE() for Brand and Occupation?
You should write "= SELECTEDVALUE...) but it wouldn't be better and would work only with a single selection.
This implementation works also with multiple selection.
@@SQLBI That’s correct. Didn’t think that way. Thanks! 👍🏻
Can this work for other kinds of slicers? Like two slicers that are set to "between"?
You may use a similar technique, but for the between it's easier to use the proper slicer in Power BI
@@SQLBI Hi there, thanks for the response. But could you ellaborate? How would I make two between slicers effact the page in an OR manner?
Between is not an or - by saying "between" you want an AND, like >= 2 AND
Thanks for sharing this Alberto, However I feel the last solution was really good but not sure about the speed when it's comes to heavy data when we have more then 20ML rows ..
The speed depends on the cardinality of the slicers (e.g. 3 items x 4 items = 12 possible combinations, which is a small number), not on the number of rows you have in the model. But if you use slicers with millions of possible selections each, you certainly have performance issues. Hundreds/thousands of items shouldn't be a big issue if you select just a few items in each slicer.
It is the best approach to learning DAX. Not sure if you can go through some of DAX patterns using the same ppproach.
We have videos for DAX Patterns available at www.daxpatterns.com explaining how the patterns work. The goal of those videos is not to teach DAX, they should speed up the ability to customize the formulas assuming the reader already knows DAX.
The solution I jumped to before even watching the video was using three calculates. Final answer equals sum of all brands for the selected occupation PLUS sum of all occupations for the selected brands MINUS sum of selected brands and occupations (to prevent double counting). Wondering how that performs compared to the set operators
This is valid only for additive measures. The technique shown in the video is measure-agnostic, it also works with non-additive measures (like DISTINCTCOUNT, time intelligence, ratios, and others).
@@SQLBI very true, great point!
Say you wanted to filter twice on the same column. One can do this with a single filter visual, the issue arises when you have two filter visuals pointing at the same column.
Reasoning: one filter visual creates a virtual table with both values. Two filter visuals add an "And" between the two values - resulting in nothing...
For example, you have a start and end date. One visual for start and one for end. If the two filters are filtering the same column... you can't find the max or min of that column...
You cannot. Two slicers on the same columns cannot be recognized in the DAX measure. You see the result of the two filters.
Why using Calculatetable and calculate in OR#2 measure?
In order to remove the filters existing on the two grouped columns without losing other filters.
Insert Meme = "But wait there's more"
Why not just calculate the units individually, 1 for occupation and one for brand and just add them up? Or maybe I'm missing something...
Are you sure that it has only 5 solutions???
All your trainings what I always keep wondering at is why don't you like giving the solution instead of paying around with different scenarios but providing your solution??
This is a very interesting question, thanks.
The reason is that I want to teach how to find a solution, rather than the solution itself. In your daily reporting requirements, you will quite never face the exact same situation I show during the demo. To me, teaching means giving you the tools needed to face any unknown scenario. If I only show one solution, maybe the best one, you will be fine if you face that scenario, but you will be in trouble with a different scenario. If, on the other hand, I teach you how to find a solution, then you will be able to solve any scenario. I know, it is a harder path... nonetheless, much more rewarding at the end.