Apply AND Logic to Multiple Selection in DAX Slicer
Vložit
- čas přidán 12. 07. 2024
- When you apply a multiple selection to a slicer or to a filter, you obtain a logical OR condition between selected items. This video shows how to implement a logical AND condition in a measure instead of the standard OR one. Article and download: sql.bi/26121?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
Thank you! I've just used this on my first foray into DAX, solving a real-world problem. You the explained it so clearly that it only took me an hour to figure out how to use this approach for our slightly different use case.
very intimating dax for a beginner, much more to learn
For beginner and for intermediate it is hard to understand. After 1,5 half year with dax i still don't understand what happened as this is not explained well imho. Would be nice to have some more detail not just straight instruction.
Thank you again for another video. This logic gives more options for DAX coders to alter the default behaviour of power bi 👍
Excellent as always! You guys are awesome!
Thank you for making these videos! Saved my day
This is really great and helped me a lot!
DAX god at it again. Great video! Thanks for posting
I'll put this video in a list named "the art of dax" I think. Awesome.
Thanks a lot Alberto !!
Thank you very much.
Hi Alberto, great tutorial, really useful as usual. Based on your example what options do I have if I need to show who are those clients ? Thank you so much.
Awesome !!!!!
Hi mate, really good video. Small tip, for the group by statement you used @ sign for '@Categories' which is actually a column title for the subsequent sumx calculation for that column. Would have been better if you did not use @ as it confused me a little where I thought it was code for something. Otherwise you present everything extremely well.
We are checking the number of categories is equal to the number of categories purchased by a given customer (2 categories in this example) but where we are making sure that its count of customers who purchased specifically these 2 categories? Please can you share your idea.
If you want to count customer that bought a certain combination of categories, you can probably apply the Survey pattern:
www.sqlbi.com/p/dax-patterns-survey/
OMG I have to watch 3 times to understand
Hi Alberto, what a great video! I got this to work in my own dashboard. My next step is to calculate the number of customers that ONLY bought products of ALL the selected categories. How should I approach this? Thanks in advance for your help!
Thanks for the video. excellent as always... could be great if you go in another video to describe how to build THIS Calculation Item! :-)
Just copy the CODE, create a calculation grup in tabular editor, a calculation item of the group, paste the code and replace the measure with SELECTEDMEASURE()
@@BernatAgulloRosello :-) thanks!
HI,
Thanks for the excellent exercises.
One step can be omitted:
Customers with all categories =
VAR NumOfSelectedCategories =
COUNTROWS ( VALUES ( 'Product'[Category] ) )
VAR CustomersAndCategories =
SUMMARIZE (
Sales,
Customer[CustomerKey],
"@Categories",DISTINCTCOUNT('Product'[Category])
)
VAR CustomersWithAllCategories =
FILTER (
CustomersAndCategories,
[@Categories] = NumOfSelectedCategories
)
Var Result = COUNTROWS (CustomersWithAllCategories)
Return Result
But this solution takes longer to execute.
The CustomersWIthNumCategories step is there to reduce the cost of the following FILTER, indeed.
Hi Alberto! A great tutorial!! But I have a problem with the last step, when you change the sales[customerkey] for customer[customerkey], I get an error message from the line of the summarize that says that customer[customerkey] isn't found in the input table. But I have the field in both of them... Could you help me? Thanks in advance!
Hi, Great Video it has really helped with a project I am working on. Is it possible to display the CustomerKey for each customer that purchased the selected items? I would love to have a second table that lists all customers that bought at least a TV and Video as well as Computers.
See CONCATENATEX: www.sqlbi.com/articles/using-concatenatex-in-measures/
@@SQLBI Thank you for your quick response!
This is so helpful as usual Alberto. For quite sometime I have been struggling to get a formula which can search each row based on few characters. In short , I want to include all rows which start with letter "K" or contains certain word in a cell ? I need to be able to do a wildcard search . Do we have something like that ?
In DAX there are text functions to do that: dax.guide/containsstring/
In Power BI you need to use some specific visual. For example, Smart Filter Pro: okviz.com/smart-filter-pro/
Is it possible to assign the selected value dynamically to pull in different column values. Say, YTD, MTD, QTD as the selected value in the slicer and they are the separate columns in time dimension. Then how is it possible to pick the YTD, MTD, QTD flags from time dimension table. Please suggest.
Awesome!
1/ In step 1 - can we use distinccount(categories) instead of countrows(valeues(catgeries))? Is there any difference?
2/ "The fact columns has no lineage so I can't use summerize" - which fact columns are considered?
Hi Alberto
In the formula you compare the number of selected categories in the slice with the number of categories that each customer bought
However if I select “computers” and “TV and video “ I expect the measure to show how many customers bought THOSE categories
It looks like your measure returns customers the bought TWO categories not the selected ones
Am I missing something ?
Thanks
I think I have to correct myself :-)
SUMMARIZE and GROUPBY are evaluated in the filter context created by the slicer so they work only considering the sales the the selected categories (two in our case).
Please let me know if I am correct.
Thanks again
Right!
hello this is chasoo kim. thank you for your excellent video.
but one thing i coundn't understand is that about data lineage.
you mentioned that the reason why you use groupby is for data lineage. could you explain this?
best regards
Look at this article+video: www.sqlbi.com/articles/understanding-data-lineage-in-dax/
How to show the name of all customers who bought from category 1 & category 2 both?
Can I use this code?
Customers with all categories Test =
VAR NumOfSelectedCategories =
COUNTROWS ( VALUES ( 'Product'[Category] ) )
VAR CustomersAndCategories =
SUMMARIZE(
Sales,
Customer[CustomerKey],
"@Categories",SUMX( VALUES( 'Product'[Category] ), 1 )
)
VAR CustomersWithAllCategories =
FILTER (
CustomersAndCategories,
[@Categories] = NumOfSelectedCategories
)
VAR Result =
COUNTROWS ( CustomersWithAllCategories )
RETURN
Result
Yes, but it's much slower (2x on this small dataset). Difference might vary, but using the aggregation in SUMMARIZE is not a good idea. See www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/