Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gng
Frequent Visitor

Customers who have NOT bought a specific product but have bought other specific product(s)

Hello! I am trying to build this report where I have to identify customers who have NOT bought a particular product but has bought other products.

I have attached an example spreadsheet which Customer, Item and Sales tables. Objective is to dynamically select an item from a slicer and the resulting table should give Customers who have NOT bought that item with a Total Sales Amount (from other products) in descending order.

 

In this example, show only those customers who bought either "x" or "w" but not "v".

 

 

ExampleProblem.PNG

1 ACCEPTED SOLUTION
gng
Frequent Visitor

I found the solution myself:

 

Measure = IF ( HASONEVALUE ( UnrelatedUniqueProductsTable [item] ), CALCULATE ( SUM ( order[Sales] ), FILTER ( order, AND(OR(order[item_id] = "36", order[item_id] = "56"), NOT ( CONTAINS ( order, order[item], VALUES ( UnrelatedUniqueProductsTable [item] ) ) )) ) ), SUM ( order[Sales] ) )

 

Thanks

G

View solution in original post

4 REPLIES 4
v-jiascu-msft
Employee
Employee

Hi @gng,

 

It seems that you already get it in your another post. All customers in the table visual have bought some products. The measure removes those customers who bought a specific product. So the table visual shows the customers who didn't buy a specific product but buy other product(s). 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-jiascu-msft,

 

I got the solution for if I choose a specific product then the table filters the data for all the customers who have not bought that product and shows sales amount for all other products for these customers.

 

Now what I am looking for is if I choose a specific product then the table should filter the data for all the customers who have not bought that product and shows sales amount for NOT 'all' but just '1 or 2 other products' for these customers.

 

So basically in the example, if product 'v' is chosen then the table should show only those customers who have bought either 'x' or 'w' or both with the corresponding sales amount instead of sales amount for all of the other products.

 

Hope this clarifies the problem.

 

 

Thanks,

G

"I got the solution for if I choose a specific product then the table filters the data for all the customers who have not bought that product and shows sales amount for all other products for these customers." 

 

Hi , How did you go about this please ?

gng
Frequent Visitor

I found the solution myself:

 

Measure = IF ( HASONEVALUE ( UnrelatedUniqueProductsTable [item] ), CALCULATE ( SUM ( order[Sales] ), FILTER ( order, AND(OR(order[item_id] = "36", order[item_id] = "56"), NOT ( CONTAINS ( order, order[item], VALUES ( UnrelatedUniqueProductsTable [item] ) ) )) ) ), SUM ( order[Sales] ) )

 

Thanks

G

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.