Customers who bought a SPECIFIC product only but not others

[Anonymous]

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.

Appreciate your help.

Thanks,

SN

[Ruksuro]

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]

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

[Ruksuro]

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]

[Zubair_Muhammad]

@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

[Anonymous]

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!!!

[Ashish_Mathur]

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?

[Zubair_Muhammad · Accepted solution]

@Anonymous

You can adjust the  MEASURE as follows

Please see attached file

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" )

[vijayks]

Zubair_Muhammad
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.

[sbonilla0809]

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 

[Ashish_Mathur]

Hi,

Have you tried my solution?

[sbonilla0809]

Hi Ashish_Mathur.

I did look at your answer but I cannot come up with the DAX code to get to your solution. Would you me so kind to post the pbix file so I can see your work.

Seems pretty good to me too

Thanks for your prompt response!

Sofi

[Ashish_Mathur]

Hi,

You may access my solution from here.

Hope this helps.

[Zubair_Muhammad]

@Anonymous

You can adjust the  MEASURE as follows

Please see attached file

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" )

[Zubair_Muhammad]

@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" )

[Anonymous]

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.