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 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!

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

5 Replies

  • 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