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 ,

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