Filter values in table based off slicer using “and” logic rather than “or” logic.

[HelpMe]

I have a matrix table with dates as the columns and then categories and topics as the rows. I have a slicer with the topics. I want to alter the logic of the slicer so that if two topics are selected I only see the categories that have both of those topics. I've tried some things from other posts similar and it's not working. Not sure if it has to do with the hierarchy of the table being that the topics are under the categories. Any help is appreciated!! 

Thanks in advance!

[v-yiruan-msft]

Hi @HelpMe ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a topics dimension table

2. Create a measure as below

Flag = 
var _topics=ALLSELECTED('Topics'[Topics])
var _category=SELECTEDVALUE('Table'[Categories])
var _tabs=CALCULATETABLE(VALUES('Table'[Topics]), FILTER(ALLSELECTED( 'Table'),'Table'[Categories]=_category&&'Table'[Topics] in _topics))
var _count1=COUNTROWS(_tabs)
var _count2=COUNTROWS(_topics)
return   if(_count1>=_count2,1,0)

3. Create a matrix visual and apply a visual-level filter with the condition(Flag is 1)

Best Regards

[HelpMe]

Hi! Thank you for the answer I think that is close to what I want. Is there a way to show only 01 and 02 I only want to see the topics selected but want to see only the categories that have both of those. So for example, if I selected 02 and 03 in the slicer I would want to see only A and B and only see 02 and 03 underneath them. Do you know if that is possible? 

[v-yiruan-msft · Accepted solution]

Hi @HelpMe ,

You can make a little adjustment on the formula of measure and get the desired result. Please find the details in the attachment.

Flag = 
var _topics=ALLSELECTED('Topics'[Topics])
var _category=SELECTEDVALUE('Table'[Categories])
var _tab1=CALCULATETABLE(VALUES('Table'[Topics]), FILTER(ALLSELECTED( 'Table'),'Table'[Categories]=_category ))
var _tab2=CALCULATETABLE(VALUES('Table'[Topics]), FILTER(ALLSELECTED( 'Table'),'Table'[Categories]=_category&&'Table'[Topics] in _topics))
var _count1=COUNTROWS(_tab2)
var _count2=COUNTROWS(_topics)
return  IF(_count1 =_count2&&COUNTROWS(_tab1)=COUNTROWS(_tab2),1,0)

Best Regards

[HelpMe]

Hi thank you that did work. I have one more question, to add some more complexity to it. What if there were duplicates? Say category A had t01 multiple times and category B had t02 multiple times? In my testing it does not work in that case how would you adapt the logic for this use case? Appreciate all the help!

[v-yiruan-msft]

Hi @HelpMe ,

It should be also work even though there are duplicated values. Could you please provide your sample pbix file to explain your problem? Thank you.

How to upload PBI in Community

Best Regards