Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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.
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"
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).
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.
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.