Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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" )
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?]
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
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!!!
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:
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?
@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
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 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?
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
Hi,
You may access my solution from here.
Hope this helps.
@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" )
@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" )
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
89 | |
86 | |
82 | |
64 | |
49 |
User | Count |
---|---|
125 | |
111 | |
88 | |
69 | |
66 |