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

Question

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.com/watch?v=X5T4rIZovHk&feature=youtu.be It seems to contain my answer, but i cant convert it to my report...

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

Behedwin · Accepted Answer · 2025-02-12 07:59:52Z

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

answered Feb 12 at 7:59

Behedwin

253 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related