Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
unkCandy
Helper I
Helper I

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

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: 

unkCandy_0-1649409024026.png

The table data looks like: 

unkCandy_1-1649409066343.png

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: 

unkCandy_2-1649409164457.png

My Slicer works as Or between two categories selected: 

unkCandy_3-1649409291080.png

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 

 

 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1649752970887.png

The final output is shown below:

vyalanwumsft_1-1649752987885.png


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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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.

vyalanwumsft_0-1649752970887.png

The final output is shown below:

vyalanwumsft_1-1649752987885.png


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
Helper I
Helper I

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
Super User
Super User

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:

MFelix_0-1649521099835.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.