Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi guys,
Need help with calculation how many customers have bought the same combination of products.
In the below example both customers A and D have purchased same products (Products: 1,2,3 and 4).
How can I find this count in PowerBI? My first thought is by creating a key for each unique product combination (not sure how the dax code would look for that) and then simply do customer count for each of those keys,.
Customer | Product |
Customer A | Product 1 |
Customer A | Product 2 |
Customer A | Product 3 |
Customer A | Product 4 |
Customer B | Product 5 |
Customer B | Product 6 |
Customer B | Product 7 |
Customer B | Product 8 |
Customer B | Product 2 |
Customer C | Product 3 |
Customer C | Product 4 |
Customer C | Product 5 |
Customer C | Product 6 |
Customer D | Product 1 |
Customer D | Product 2 |
Customer D | Product 3 |
Customer D | Product 4 |
Please help!
Solved! Go to Solution.
Summary Table =
var temp= SUMMARIZE(Sheet5,Sheet5[Customer],"Combinations",CONCATENATEX(FILTER(SUMMARIZE(Sheet5,[Customer],Sheet5[Type]),Sheet5[Customer]=EARLIER(Sheet5[Customer])),[Type]&","))
return
SUMMARIZE(temp,[combinations],"Number",COUNTAX(FILTER(temp,[combinations]=EARLIER([combinations])),[Customer]))
Modify It According to your needs
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Summary Table =
var temp= SUMMARIZE(Sheet5,Sheet5[Customer],"Combinations",CONCATENATEX(FILTER(SUMMARIZE(Sheet5,[Customer],Sheet5[Type]),Sheet5[Customer]=EARLIER(Sheet5[Customer])),[Type]&","))
return
SUMMARIZE(temp,[combinations],"Number",COUNTAX(FILTER(temp,[combinations]=EARLIER([combinations])),[Customer]))
Modify It According to your needs
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
Hi mh2587, thank you for the help, it works, however I am now unable to link the summarized table to the existing fact table (to be able to set filters on date or bring some other columns like the customer name for some investigation). Any idea on how I acomplish this?
would you please share the model
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!
apologies, the data is confidential but here's how the two tables look in the model:
I have the summary table you suggested and need a way to see all the Opportunities that belong under each "combination".
Also, I will need to filter the Summary table by a specific "Opportunity Close Date", column that belongs to the fact table.
Is there a way I can make a relationship between the two tables? Or make the summary table as columns on the fact table?
thank you, it works! now only thing i have to do is find a way to link it to the other tables 🙂
Check this one hope it will suit your requirements
https://www.daxpatterns.com/basket-analysis/
Did I answer your question? If so, please mark my post as a solution!
Proud to be a Super User!