MULTIPLE FILTERS WITH OR CONDITION

Question

Hi Everyone,&nbsp;I have been looking for a way to make a custom slicer that filters on AND instead of OR, so far unsuccessfully. Below is some example data to further describe what I am looking for.&nbsp;&nbsp;Base Data:NameApplesOrangesBananasPearsVendor 1TRUEFALSETRUEFALSEVendor 2FALSEFALSETRUETRUEVendor 3FALSETRUEFALSEFALSEVendor 4TRUETRUETRUEFALSEVendor 5TRUETRUETRUETRUE&nbsp;Now I want to be able to use one slicer instead of 5 different one built on AND conditions when I am looking for a Vendor that fulfils all criteria’s inserted. E.g. If i am looking for a vendor that sells all fruits then only vendor 5 should remain in my results. Is there some way to build a custom slicer in Power BI which then can communicate with the other Slicers to filter the data?&nbsp;&nbsp;I've also tried to change the data structure to:NameFruitsVendor 1ApplesVendor 1BananasVendor 2Bananas&nbsp;Vendor 2Pearsetcetc&nbsp;And only apply a Slicer on Fruits. This however still operates with an OR command and will keep all vendors that fulfils at least one of the criteria’s, while I want to have an AND command for multiple columns.&nbsp;&nbsp;The custom slicer I want to build would look something like:FruitsFilter AND?ApplesTRUEOrangesTRUEBananas&nbsp;FALSEPearsFALSE&nbsp;Which should return Vendor 4 &amp; 5 in my map.Is there any solutions to this?&nbsp;

v-jiascu-msft · Accepted Answer

Hi&nbsp;Dalen&nbsp;,
&nbsp;
There is a workaround. Please download the demo from the attachment.&nbsp;
1. Add a new column to connect two fields.
2. Create a new table.
3. Create a new measure as a Flag.
Flag&nbsp;=
IF&nbsp;(
&nbsp;&nbsp;&nbsp;&nbsp;ISFILTERED&nbsp;(&nbsp;FruitStatus[Column]&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp;&nbsp;CALCULATE&nbsp;(&nbsp;COUNT&nbsp;(&nbsp;Table1[Column]&nbsp;),&nbsp;ALL&nbsp;(&nbsp;Table1[Fruit],&nbsp;Table1[Value]&nbsp;)&nbsp;)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=&nbsp;CALCULATE&nbsp;(&nbsp;COUNT&nbsp;(&nbsp;FruitStatus[Column]&nbsp;),&nbsp;ALLSELECTED&nbsp;(&nbsp;FruitStatus[Column]&nbsp;)&nbsp;),
&nbsp;&nbsp;&nbsp;&nbsp;1,
&nbsp;&nbsp;&nbsp;&nbsp;BLANK&nbsp;()
)

&nbsp;
&nbsp;
Best Regards,

Forum Discussion

MULTIPLE FILTERS WITH OR CONDITION

1 Reply

Related content

Multiple conditions Index

Multiple Donut charts with conditions

Conditional Column with Multiple Conditions

Advanced filtering in power query - multiple conditions

Filtering a table with multiple conditions from multiple switches pulled from another table

Name	Apples	Oranges	Bananas	Pears
Vendor 1	TRUE	FALSE	TRUE	FALSE
Vendor 2	FALSE	FALSE	TRUE	TRUE
Vendor 3	FALSE	TRUE	FALSE	FALSE
Vendor 4	TRUE	TRUE	TRUE	FALSE
Vendor 5	TRUE	TRUE	TRUE	TRUE

Name	Fruits
Vendor 1	Apples
Vendor 1	Bananas
Vendor 2	Bananas
Vendor 2	Pears
etc	etc

Fruits	Filter AND?
Apples	TRUE
Oranges	TRUE
Bananas	FALSE
Pears	FALSE