dax - How to filter PowerBi table based on multiselect slicer - combination of selected slicer values - Stack Overflow

admin管理员组

文章数量:1296392

have this setup

Table1: Customers - Column: CustomerId, Name Table2: Products - Column: ProductId, Name Table3: CustomerProduct - Column: ProductsId, CustomersId

Relationship: 1-to-many between Customers[CustomerId] > CustomerProduct[CustomersId] Relationship: 1-to-many between Products[ProductId] > CustomerProduct[ProductsId]

---

On my report i have 1 slicer that is based on Products[Name] -slicer is setup to allow multiple selections.

I have one table visual that have Customers[Name]

I want to filter my table visual - when i select two or more products in the slicer. I want to see Customers that have ALL selected products.

Example:

I select Product "aa" - that product is owned by customer "A" and "E"

• table shows now customer A and E.

I then ALSO select Product "cc" - that product is owned by customer "E" and "J"

• table shows now only customer E, since customer E is the only owner of product "aa" and "cc".

I then reset my selection (in slicer) and select Product "aa" and "bb"

• table show now only customer A and E since both selected products is owned by customer A and E.

If slicer have no selections, table should be empty. All selected products in slicer must be owned by the Customer showed in the table.

EXAMPLE FILE:

I have tried several different methods. I am not that good with DAX and have mostly searched around using normal Googling and AI to try and figure out a method on how to do this. But so far i have not been able. I found a youtube video here: ;feature=youtu.be It seems to contain my answer, but i cant convert it to my report...

have this setup

Table1: Customers - Column: CustomerId, Name Table2: Products - Column: ProductId, Name Table3: CustomerProduct - Column: ProductsId, CustomersId

Relationship: 1-to-many between Customers[CustomerId] > CustomerProduct[CustomersId] Relationship: 1-to-many between Products[ProductId] > CustomerProduct[ProductsId]

---

On my report i have 1 slicer that is based on Products[Name] -slicer is setup to allow multiple selections.

I have one table visual that have Customers[Name]

I want to filter my table visual - when i select two or more products in the slicer. I want to see Customers that have ALL selected products.

Example:

I select Product "aa" - that product is owned by customer "A" and "E"

• table shows now customer A and E.

I then ALSO select Product "cc" - that product is owned by customer "E" and "J"

• table shows now only customer E, since customer E is the only owner of product "aa" and "cc".

I then reset my selection (in slicer) and select Product "aa" and "bb"

• table show now only customer A and E since both selected products is owned by customer A and E.

If slicer have no selections, table should be empty. All selected products in slicer must be owned by the Customer showed in the table.

EXAMPLE FILE: https://1drv.ms/u/c/7fb4de7a296ed17a/EcwRtrdY6HZPvLmIzfbmXxgBmPwY1PedlDIp2FtgcqYxiw?e=7tMfNe

I have tried several different methods. I am not that good with DAX and have mostly searched around using normal Googling and AI to try and figure out a method on how to do this. But so far i have not been able. I found a youtube video here: https://www.youtube/watch?v=X5T4rIZovHk&feature=youtu.be It seems to contain my answer, but i cant convert it to my report...

• powerbi
• dax
• report
• slicers

Share Improve this question Follow asked Feb 11 at 19:38 BehedwinBehedwin 3733 silver badges99 bronze badges 1

• not clear about your question. What's the expected output? when you select aa and cc, which customer do you want to display? – Ryan Commented Feb 12 at 0:50

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

I solved it. This was my solution. Create a meassure

Has All Selected Products = 
VAR SelectedProducts = VALUES(Products[ProductId])
VAR CustomerProducts = 
    CALCULATETABLE(
        VALUES(CustomerProduct[ProductsId]),
        ALLEXCEPT(Customers, Customers[CustomerId])
    )
VAR IntersectedProducts = INTERSECT(SelectedProducts, CustomerProducts)
RETURN
IF(
    COUNTROWS(IntersectedProducts) = COUNTROWS(SelectedProducts),
    1,
    0
)

Then filter the table visual on this measure equal 1

本文标签：