relationship filter in powerBI or powerpivot

Question

i am using the powerBI tools (powerpivot) to create a data model. i am done the model. the model include the product, customer dimensions and sales fact table. i have made the relationship and hierarchy in the model. now i have a requirement to show the total revenue of all the customer who brought product 1,2,3.

for example customer A brought product 1 and product 5 and the total revenue from this customer is 50 so i want to show 50 as a result

customer B bought product 4 and i do not want to include this customer in my output.

i can do the same in microstratergy using relationship filter but how can i do the same in powerpivot or powerview or powerBI.

Please help Thanks in Advance

Can you add data model to question? Also can you elaborate on desired result. Do you need total revenue of customer or revenue generated from product 1,3, 5 only? — Abhijeet Nagre
I Want the total revenue of the customer not the revenue generated from the product 1,3,5 only — Atul Bansal

Abhijeet Nagre Abhijeet Nagre · Accepted Answer · 2015-05-28T06:58:06

In PowerPivot, relate Sales table with Customer table (Lookup table) and Relate Sales table to Product table (Lookup table).

Create following two measures

[HasPurchased X Products] =
OR (
    OR (
        CONTAINS ( Sales, Sales[ProductID], 1 ),
        CONTAINS ( Sales, Sales[ProductID], 3 )
    ),
    CONTAINS ( Sales, Sales[ProductID], 5 )
)

[DesiredMeasure] = 
IF (
    Sales[HasPurchased X Products] = TRUE (),
    SUM ( [Amount] ),
    BLANK ()
)

Select Customers in ROWS and add [DesiredMeasure] in VALUES, pivot table will show desired result.

relationship filter in powerBI or powerpivot

2 Answers