Forum Discussion
Combining Filter with AND
Ah, you desire the mythical AND Slicer. Luckily, it exists. This one is of my own invention and is in my book actually:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130
With an explanation because the way it works is not obvious at all. But, I think it is the most concise AND slicer out there if not intuitive in how it operates.
FILTER('Table', [Column1] = "A" && [Column2] = "B")
Hi Greg_Deckler ,
thanks for the quick response. The data structure is as Follows:
Customer:
Customer Name Customer ID Foo 123 Bar 456 Contracts:
ID CustomerID Type C12 123 A C13 123 B C14 456 A C15 456 B C16 456 E I now want to ad a filter to the report, so that the report users can analyze which customers have contracts of type "A AND B" or "A AND B AND E" (Results in Customer 456, named Bar).
Ah, you desire the mythical AND Slicer. Luckily, it exists. This one is of my own invention and is in my book actually:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130
With an explanation because the way it works is not obvious at all. But, I think it is the most concise AND slicer out there if not intuitive in how it operates.
Hi , frieso
Is this the result you want? pbix attached
If so ,please create a visual control measure and apply it to the table filter pane.
Measure = VAR tab = FILTER ( ALL ( Contracts ), Contracts[Type] IN FILTERS ( 'Table'[Type] ) ) VAR numoftypes = DISTINCTCOUNT ( 'Table'[Type] ) VAR _customerid = SELECTEDVALUE ( Contracts[CustomerID] ) VAR num = COUNTROWS ( FILTER ( tab, [CustomerID] = _customerid ) ) RETURN IF ( ISFILTERED ( 'Table'[Type] ), IF ( SELECTEDVALUE ( Contracts[Type] ) IN FILTERS ( 'Table'[Type] ), IF ( numoftypes = num, 1, 0 ), 0 ), 0 )Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
