Forum Discussion

unkCandy's avatar
unkCandy
Icon for Helper I rankHelper I
4 years ago
Solved

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: 

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 

 

 

 

Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

3 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    4 years ago

    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:

     

     

  • unkCandy's avatar
    unkCandy
    Icon for Helper I rankHelper I
    4 years ago

    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