Apply AND Logic to Multiple Selection in DAX Slicer

[@kateanderson1138]

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.

[@Darknesslc3]

very intimating dax for a beginner, much more to learn

[@Wzxxx]

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.

[@joserafaelfarfanfernandez899]

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.

[@Miekjaah]

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!

[@Bharath_PBI]

Thank you again for another video. This logic gives more options for DAX coders to alter the default behaviour of power bi 👍

[@defin8lykrithi]

Thank you for making these videos! Saved my day

[@BernatAgulloRosello]

I'll put this video in a list named "the art of dax" I think. Awesome.

[@Paul-tm6lw]

DAX god at it again. Great video! Thanks for posting

[@triparnaray]

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.

[@SQLBI]

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/

[@DanielADamico]

Excellent as always! You guys are awesome!

[@eddyjawed4871]

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.

[@Brezisnki72]

I have two tables and have a slicer selecting multipule components to return all the master items containing only these selected components. However, I am still getting a return of all items with selected from slicer.

[@michaelribbins5708]

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.

[@SQLBI]

See CONCATENATEX: www.sqlbi.com/articles/using-concatenatex-in-measures/

[@michaelribbins5708]

@@SQLBI Thank you for your quick response!

[@Mateusz_92]

This is really great and helped me a lot!

[@yacobberk3931]

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.

[@SQLBI]

The CustomersWIthNumCategories step is there to reduce the cost of the following FILTER, indeed.

[@hemanadezhdank401]

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.

[@nupursrivastava6971]

How to show the name of all customers who bought from category 1 & category 2 both?

[@mariavillanueva3599]

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!

[@AlbertoGastaldo]

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

[@AlbertoGastaldo]

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

[@SQLBI]

Right!

[@김차수-k2u]

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

[@SQLBI]

Look at this article+video: www.sqlbi.com/articles/understanding-data-lineage-in-dax/

[@hlambert100]

Thank you very much.

[@zeisslaimen1]

OMG I have to watch 3 times to understand

[@powerbiisrael6819]

Thanks a lot Alberto !!

[@raulvera705]

Thanks for the video. excellent as always... could be great if you go in another video to describe how to build THIS Calculation Item! :-)

[@BernatAgulloRosello]

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

[@raulvera705]

@@BernatAgulloRosello :-) thanks!

[@KamalKumar-fl2zb]

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 ?

[@SQLBI]

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/

[@pritammusale7858]

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

[@SQLBI]

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/

[@pedroperentivitor5503]

Awesome !!!!!

[@eduardomunoz2764]

Awesome!

[@Wzxxx]

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?