cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Customers who bought a SPECIFIC product only but not others

Hi, I am trying to solve a reporting need. Following is my sample data and I am only looking to output customers who only bought a particular product(ex. only apple watch) but not other products.

 Cust ID Order Id Product 123 1 iPhone X 123 1 iPod 123 1 Apple Watch 53 2 iPhone 8s 53 2 Airport 2442 3 Apple Watch 782 4 Apple TV 782 4 Macbook Air 428 5 Apple Watch 53 6 Apple Watch Output - Customers who only bought Apple Watch Cust ID 2442 428

As you can see, though customers 53 and 123 did bought Apple watch, they are not output because they also bought other products.

Thanks,

SN

1 ACCEPTED SOLUTION
Community Champion

@Anonymous

You can adjust the  MEASURE as follows

If you include Sale Date

```Measure =
VAR selectedProduct =
VALUES ( Table1[Product] )
VAR Cond1 =
COUNTROWS ( VALUES ( Table1[Product] ) )
VAR Cond2 =
COUNTROWS (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER (
ALL ( Table1[Product], Table1[Sale Date] ),
NOT [Product] IN selectedProduct
)
)
)
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

If you want to include store ID

```Measure 2 =
VAR selectedProduct =
VALUES ( Table1[Product] )
VAR Cond1 =
COUNTROWS ( VALUES ( Table1[Product] ) )
VAR Cond2 =
COUNTROWS (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER (
ALL ( Table1[Product], Table1[store ID] ),
NOT [Product] IN selectedProduct
)
)
)
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

Regards
Zubair

15 REPLIES 15
Helper III

Just in case Zubair's solution is too specific to a single case:

I would group by Customer and aggregate as a list of products purchased (new table):

Cust ID | Purchase history

123, iPhoneX, iPod, Apple Watch

Then filter that dataset (advanced filters) as - Contains [Desired Output] AND Does not contain [Exclusions].

This is possible in SQL or M-Query I believe, what format is your source data?

[I suppose you could parameterise Zbair solution instead?]

Anonymous
Not applicable

Agree, I want something generic, may be use product as a slicer and make it generic. My source is tabular model.

Helper III

Apologies for the slow reply, I had some example SQL code that might help at work but didn't have access until now. It's psuedocode but the idea worked for me.

select
[Unique Identifier],

stuff(
(Select distinct (', ' + Product) from #your_sales_table
where ([Unique Identifier] =  [Unique Identifier]) order by (', ' + Product)
for XML PATH('')),1,2,'')
as [Purchase History]
from #your_sales_table
group by [Unique Identifier]

Community Champion

@Anonymous

If you want it be based on slicer selection , you can use this

Please see the attached file for clarity

```Measure =
VAR selectedProduct =
VALUES ( Table1[Product] )
VAR Cond1 =
COUNTROWS ( VALUES ( Table1[Product] ) )
VAR Cond2 =
COUNTROWS (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER ( ALL ( Table1[Product] ), NOT [Product] IN selectedProduct )
)
)
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

I didn't get your second requirement....could you illustrate it with an example

Regards
Zubair

Anonymous
Not applicable

Appreciate your help here Zubair. I have revised the dataset to better explain my requirement and I apologise for not being clear.

 Cust ID Order Id Store ID Sale Date Product 123 1 100 5/11/2018 iPhone X 123 1 100 5/11/2018 iPod 123 1 100 5/11/2018 Apple Watch 53 2 165 3/23/2016 iPhone 8s 53 2 165 3/23/2016 Airport 2442 3 354 7/15/2017 Apple Watch 782 4 543 9/30/2015 Apple TV 782 4 543 9/30/2015 Macbook Air 428 5 461 1/18/2018 Apple Watch 53 6 45 3/13/2018 Apple Watch 324 16 740 8/14/2018 Macbook Air Output - Customers who only bought Apple Watch Cust ID Sale Date Product 2442 7/15/2017 Apple Watch 428 1/18/2018 Apple Watch OR Cust ID Store ID Product 2442 354 Apple Watch 428 461 Apple Watch OR - Customers who only bought Macbook Air Cust ID Store ID Product 324 749 Macbook Air

What this means I should be able to slice and dice on differnt fields available in this table. Hope this clarifies. The other thing is, I am over simplifying my data(sample) here but I have a typical dimensional model. I can tweak your DAX query to suit my model.

Thanks a million!!!

Super User

Hi,

Mine is a slightly different approach.  In the image below, there are 3 sections - a Table at the top left, 2 slicers and a table at the bottom:

1. Table at the top left - This shows you which product did customers buy/did not buy along with a "Mother product".  Let's assume that the products listed in the Row labels are the Mother products.  So the Apple Watch is a Mother product and there were 4 customers that purchased this product (value at the intesection point of Apple Watch appearing in the row labels and Apple Watch appearing in the column labels).  Those 4 customers also purchased the Airport, IPhone 8S, Iphone X and Ipod.  You can also see the Customer ID's of who purchased or did not purchase.  I understand this is not your expected result but as i was thinking of solving your question, it occurred that this would be "good to have information".
2. Slicers - For Year and month.  You can slice the top and bottom tables by Year/Month
3. Table at the bottom left - This is the Table you actually want (your desired result)

You may of course delete the first table and just keep the second one.  From the first table, if you wish, you may also remove the Customer ID's (see image below)

Is this something you are interested in?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

@Anonymous

You can adjust the  MEASURE as follows

If you include Sale Date

```Measure =
VAR selectedProduct =
VALUES ( Table1[Product] )
VAR Cond1 =
COUNTROWS ( VALUES ( Table1[Product] ) )
VAR Cond2 =
COUNTROWS (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER (
ALL ( Table1[Product], Table1[Sale Date] ),
NOT [Product] IN selectedProduct
)
)
)
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

If you want to include store ID

```Measure 2 =
VAR selectedProduct =
VALUES ( Table1[Product] )
VAR Cond1 =
COUNTROWS ( VALUES ( Table1[Product] ) )
VAR Cond2 =
COUNTROWS (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER (
ALL ( Table1[Product], Table1[store ID] ),
NOT [Product] IN selectedProduct
)
)
)
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

Regards
Zubair

New Member

I want exact requirement as you have done now, but I want that count into dax result.<br />I mean to say you created a filter "Yes" and then putting this filter on visual level its showing correct result into table , but i want that&nbsp; result count into dax measure to show the count into card value. Please help me its is very urgent if you would help to me.

Frequent Visitor

Hi @Zubair_Muhammad  I know this post was a while ago.

I looked into it and it was super helpful. Thank you so much for sharing your knowledge.

I have one more question tho:

How can I make a count of the customers that satisfy these conditions? For instance, the Apple Watch customers count would be 2.

I tried pulling the 'Measure' ( you created)  to the Filter area of my CARD with a simple count customer:= count(customerid) but it didn't work.

You think you can help me with this? 😁

Thanksss

Super User

Hi,

Have you tried my solution?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Super User

Hi,

You may access my solution from here.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Champion

@Anonymous

You can adjust the  MEASURE as follows

If you include Sale Date

```Measure =
VAR selectedProduct =
VALUES ( Table1[Product] )
VAR Cond1 =
COUNTROWS ( VALUES ( Table1[Product] ) )
VAR Cond2 =
COUNTROWS (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER (
ALL ( Table1[Product], Table1[Sale Date] ),
NOT [Product] IN selectedProduct
)
)
)
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

If you want to include store ID

```Measure 2 =
VAR selectedProduct =
VALUES ( Table1[Product] )
VAR Cond1 =
COUNTROWS ( VALUES ( Table1[Product] ) )
VAR Cond2 =
COUNTROWS (
CALCULATETABLE (
VALUES ( Table1[Product] ),
FILTER (
ALL ( Table1[Product], Table1[store ID] ),
NOT [Product] IN selectedProduct
)
)
)
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

Regards
Zubair

Community Champion

@Anonymous

One way is to create this MEASURE and then put it as a VISUAL filter

```Measure =
VAR Cond1 =
COUNTROWS ( FILTER ( VALUES ( Table1[Product] ), [Product] = "Apple Watch" ) )
VAR Cond2 =
COUNTROWS ( FILTER ( VALUES ( Table1[Product] ), [Product] <> "Apple Watch" ) )
RETURN
IF ( AND ( Cond1 = 1, Cond2 = 0 ), "Yes" )
```

Regards
Zubair

Anonymous
Not applicable

Thanks Zubair. This works for the problem I described. However, do you think we can customize it for the following:

- I want the product to be dynamic ie. not just "Apple Watch", if user decides they want other product, they should be able to do it. (May be passin the slicer selction to the measure calculation)

- Apart from cust id, I also want to display some other attributes related to the sale. ex. Store ID, Purchase date etc. FYI, when I bring in other field,( Order Id) this approach is breaking. It brings Cust id 53.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors