Slicer with AND condition in Power BI For Many To Many relationship

[unkCandy]

Hello Community, 

I would like for the multiselection in the slicer to get back the AND between the selection instead of OR. I found on the internet many articles on how to do it but the relationship between their table is one to many, and in my case is Many To Many as my id is duplicated. 

My model is as follows: 

The table data looks like: 

The reason why I have Many to Many relationships as I have for each id is there is a flow to products and each has a category, my category filter table looks like this: 

My Slicer works as Or between two categories selected: 

Thank you for reading, In summary, I want to have a slicer with And logic when the relationship between the tables is Many To Many. 

Here is the pbix: https://drive.google.com/file/d/1bjP4kHN3YL3c5zEZ8Nf7-QGp9OXIwAma/view?usp=sharing 

[v-yalanwu-msft · Accepted solution]

Hi, @unkCandy ;

You could create a flag measure.

flag = 
var _count= CALCULATE(DISTINCTCOUNT([Category]),FILTER(ALLEXCEPT('data','data'[Id]),[Category] in ALLSELECTED('Category Filter'[Category])))
var _count2=CALCULATE(DISTINCTCOUNT('Category Filter'[Category]),REMOVEFILTERS('data'))
return IF(_count=_count2,1)

Then apply it into table filter.

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[unkCandy]

Hello Mfelix, thank you for your reply and your time, but maybe I wasn't clear about my problem.

Actually, I created the other table Category Filter to get all products per ID when a category is included in the ID. Now I want if I select two categories to give me the IDs that have both categories, with all products. 

I hope I was clear. and thank you again 

[MFelix]

Hi  @unkCandy .

Try to add the following measure to your  model:

Filtering on Category = COUNTROWS(FILTER(data, data[Category] in VALUES('Category Filter'[Category])))

Now add this has a visual filter and select all non blank values: