Combining Filter with AND

[frieso]

Hello Community,

I have two Tables: Customers and Contracts. In the contracts table I have one column "type". I then have a matrix where I display per client which typ's of contracts he has. 

I want now to be able to filter the customers by contract typs. This works but it filters the customer with "OR" on every type I filter. How could I set the filter to "AND" the typs in the way: all customers that have e contract of type A AND B AND E? 

Thanks for your help!

[amitchandak]

If you need on of the value

Table[ e contract of type] in {"A", "B","E"}

I am not how both can be true , but this example of and

Table[ e contract of type]= "A" && Table[ e contract of type]= "B" && Table[ e contract of type]= "E"

Example of OR

Table[ e contract of type]= "A" || Table[ e contract of type]= "B" || Table[ e contract of type]= "E"

[Greg_Deckler]

FILTER('Table', [Column1] = "A" && [Column2] = "B")

[frieso]

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

[v-easonf-msft]

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.

[Greg_Deckler · Accepted solution]

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.