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!