Forum Discussion

HelpMe's avatar
HelpMe
New Member
2 years ago
Solved

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

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
HelpMe
New Member
2 years ago

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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      HelpMe
      New Member
      2 years ago

      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!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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