Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
I have a large historical dataset of customers and the services (Plans) they had bought from my company. I am working on a Cross-Selling analysis project.
Based on this data, I want to see the names of the customers who (for example) bought Plan 4 and Plan 10.
Note: I want to see only the customers who bought both of these plans. Not individually.
Solved! Go to Solution.
@alir22456 Review the attached screenshot and DAX code to get the desired results.
Cohort =
VAR __tmpTable1 =
GENERATE(
DISTINCT('Table'[Customers]),
EXCEPT(
DISTINCT('Table'[Services]),
CALCULATETABLE(DISTINCT('Table'[Services]))
)
)
VAR __tmpTable2 = SUMMARIZE(__tmpTable1, 'Table'[Customers])
VAR __tmpTable3 = EXCEPT(DISTINCT('Table'[Customers]), __tmpTable2)
VAR __Result = CONCATENATEX(__tmpTable3, [Customers], ",", [Customers])
RETURN
__Result
SelectedPlans =
VAR SelectedPlansTable = VALUES('Table'[Services])
RETURN
IF(
ISBLANK(CONCATENATEX(SelectedPlansTable, 'Table'[Services], " and ")),
"No Plan Selected",
CONCATENATEX(SelectedPlansTable, 'Table'[Services], " and ")
)
Hope it works.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Thanks @fahadqadir3 for your solution,
Can you please tell if there is a way to see the list of customers who bought Plan 10 minus the customers who bought Plan 10 and Plan 4?
Targeted Customers = Plan 10 Customers - (Plan 10 Customers and Plan 4 Customers)
@alir22456 Review the attached screenshot and DAX code to get the desired results.
Cohort =
VAR __tmpTable1 =
GENERATE(
DISTINCT('Table'[Customers]),
EXCEPT(
DISTINCT('Table'[Services]),
CALCULATETABLE(DISTINCT('Table'[Services]))
)
)
VAR __tmpTable2 = SUMMARIZE(__tmpTable1, 'Table'[Customers])
VAR __tmpTable3 = EXCEPT(DISTINCT('Table'[Customers]), __tmpTable2)
VAR __Result = CONCATENATEX(__tmpTable3, [Customers], ",", [Customers])
RETURN
__Result
SelectedPlans =
VAR SelectedPlansTable = VALUES('Table'[Services])
RETURN
IF(
ISBLANK(CONCATENATEX(SelectedPlansTable, 'Table'[Services], " and ")),
"No Plan Selected",
CONCATENATEX(SelectedPlansTable, 'Table'[Services], " and ")
)
Hope it works.
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
Hi @fahadqadir3
Thank you for the above solution. Can you please tell if each customer name in the visualization can be displayed in separate rows instead of comma separated.
User | Count |
---|---|
120 | |
65 | |
62 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
69 | |
62 | |
55 |