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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

15 REPLIES 15
Ruksuro
Helper III
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.

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]

@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

 

123213.png


Regards
Zubair

Please try my custom visuals
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 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!!!

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:

 

Untitled.png

 

  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)

 

Untitled.png

 

Is this something you are interested in?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Regards
Zubair

Please try my custom visuals

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.

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 

Hi,

Have you tried my solution?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi 

 

 

 

Hi,

You may access my solution from here.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Regards
Zubair

Please try my custom visuals
Zubair_Muhammad
Community Champion
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" )

312735.png


Regards
Zubair

Please try my custom visuals
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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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