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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alir22456
Frequent Visitor

Power Query

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.

alir22456_3-1720672934544.png

 

Based on this data, I want to see the names of the customers who (for example) bought Plan 4 and Plan 10.

alir22456_4-1720672948204.png

Note: I want to see only the customers who bought both of these plans. Not individually. 

1 ACCEPTED SOLUTION
fahadqadir3
Responsive Resident
Responsive Resident

@alir22456 Review the attached screenshot and DAX code to get the desired results.

fahadqadir3_0-1720675286351.png

 

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

View solution in original post

2 REPLIES 2
alir22456
Frequent Visitor

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)

fahadqadir3
Responsive Resident
Responsive Resident

@alir22456 Review the attached screenshot and DAX code to get the desired results.

fahadqadir3_0-1720675286351.png

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

Top Solution Authors