Filtering a data set based on the result of a filtered table

[andyhowes64]

Hi.

I'm very new to Power BI and could do with some help regarding filtering data.

I have a query pulling in a sales list of around 500,000 rows of data.  This data contains around 3,000 distinct products and 2,000 customers.

What I would like to do is create a slicer where a user can select a particular product from the list, this identifies all customers who have bought that product and then returns a list of all products bought by these customers.

As an example, from the below table the user would select product A, which has only been purchased by Customers 1 & 2:

Product	Customer	Revenue
A	1	99
A	1	36
A	2	80
A	2	15
A	1	23
A	2	64
B	2	45
B	1	99
B	4	88
B	3	62
B	2	5
B	1	8
C	3	47
C	3	70
C	4	67
C	4	35
C	3	74
C	3	68

The output table would then filter the above table for customer 1 & 2 returning all the products that they have purchased:

Product	Customer	Revenue
A	1	99
A	1	36
A	2	80
A	2	15
A	1	23
A	2	64
B	2	45
B	1	99
B	2	5
B	1	8

The filtering would need to be based off of a user friendly filtering method, which from my limited experience appears to be slicers.

Apologies if this is very basic and I have missed something simple.  I have spent a few hours looking for a possible solution but have ended up down quite a few dead ends. Whilst I am new to Power BI, I have some experience of Power Query and can merge and filter queries as required.

Thanks in advance for any help.

Andy 

[v-cherch-msft · Accepted solution]

Hi @andyhowes64 

You may create a slicer table then use it as slicer.Make sure there's no relationship.Then new a measure to filter the table.

New Table:

Slicer = DISTINCT(Data[Product])

Measure =
VAR cus =
    CALCULATETABLE (
        VALUES ( Data[Customer] ),
        FILTER ( ALL ( Data ), Data[Product] IN VALUES ( Slicer[Product] ) )
    )
RETURN
    IF ( MAX ( Data[Customer] ) IN cus, 1 )

Regards,

[andyhowes64]

Thanks @v-cherch-msft  this is awesome.  You've saved me a lot of potential workarounds to do this.

Thanks again.

Andy