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.
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).
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.