cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srikanthnama Regular Visitor
Regular Visitor

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 IDOrder IdProduct
1231iPhone X
1231iPod
1231Apple Watch
532iPhone 8s
532Airport
24423Apple Watch
7824Apple TV
7824Macbook Air
4285Apple Watch
536Apple 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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Customers who bought a SPECIFIC product only but not others

@srikanthnama

 

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" )
10 REPLIES 10
Super User
Super User

Re: Customers who bought a SPECIFIC product only but not others

@srikanthnama

 

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

312735.png

Highlighted
Ruksuro Member
Member

Re: Customers who bought a SPECIFIC product only but not others

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?]

srikanthnama Regular Visitor
Regular Visitor

Re: Customers who bought a SPECIFIC product only but not others

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

Ruksuro Member
Member

Re: Customers who bought a SPECIFIC product only but not others

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]

srikanthnama Regular Visitor
Regular Visitor

Re: Customers who bought a SPECIFIC product only but not others

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.

Super User
Super User

Re: Customers who bought a SPECIFIC product only but not others

@srikanthnama

 

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

 

123213.png

srikanthnama Regular Visitor
Regular Visitor

Re: Customers who bought a SPECIFIC product only but not others

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

 

Cust IDOrder IdStore IDSale DateProduct
12311005/11/2018iPhone X
12311005/11/2018iPod
12311005/11/2018Apple Watch
5321653/23/2016iPhone 8s
5321653/23/2016Airport
244233547/15/2017Apple Watch
78245439/30/2015Apple TV
78245439/30/2015Macbook Air
42854611/18/2018Apple Watch
536453/13/2018Apple Watch
324167408/14/2018Macbook Air
     
Output - Customers who only bought Apple Watch 
Cust IDSale DateProduct  
24427/15/2017Apple Watch  
4281/18/2018Apple Watch  
     
OR    
     
Cust IDStore IDProduct  
2442354Apple Watch  
428461Apple Watch  
     
     
OR - Customers who only bought Macbook Air 
Cust IDStore IDProduct  
324749Macbook 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
Super User

Re: Customers who bought a SPECIFIC product only but not others

@srikanthnama

 

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

Re: Customers who bought a SPECIFIC product only but not others

@srikanthnama

 

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