Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey folks,
I'm facing the following issue and I'd like your help in solving it.
The request from colleagues:
I'd like to see a list of customers who purchased Product X but didn't purchase Product Y and I'd like to be able to choose which products I'm filtering on.
How I would like to present this:
I'd like to give them two slicers. One would let them choose the product a customer purchased and the other one would let them choose the product that wasn't purchased. (and then a table would just the list the customer names)
The problem:
Obviously, I can do a slicer on Product Name to see who purchased Product X but I don't know how to create another slicer for filtering out Product Y (which would allow users to see that amongst the customers who purchased Product X who are the ones who didn't purchase Product Y).
Additional note:
A customer could have purchased only one product.
The data set:
Table: AllAccount
Dimensions: Customer Name, Product Name, Products Concatenated [all of the respective customer's products in a comma separated list]
Customer Name | Product Name | Products Concatenated |
Albert Einstein | Product A | Product A, Product X |
Albert Einstein | Product X | Product A, Product X |
Marie Curie | Product B | Product B, Product X, Product Y |
Marie Curie | Product X | Product B, Product X, Product Y |
Marie Curie | Product Y | Product B, Product X, Product Y |
Herman Muller | Product C | Product C, Product Y, Product Z |
Herman Muller | Product Y | Product C, Product Y, Product Z |
Herman Muller | Product Z | Product C, Product Y, Product Z |
Marthin Luther King, Jr. | Product X | Product X, Product Z |
Marthin Luther King, Jr. | Product Z | Product X, Product Z |
Expected result on who purchased Product X but didn't purchase Product Y:
Albert Einstein
Martin Luther King, Jr.
Thank you very much in advance!
Cheers,
Nora
Solved! Go to Solution.
You can create two slicer tables as:
Is_Purchased =
VALUES('Table'[Product Name])
Not_Purchased =
VALUES('Table'[Product Name])
Create measure as:
Measure =
IF(
CONTAINSSTRING(MAX('Table'[Products Concatenated]),ALLSELECTED(Is_Purchased)),
IF(
CONTAINSSTRING(MAX('Table'[Products Concatenated]),ALLSELECTED(Not_Purchased)),
0,
1),
0
)
Here is the output:
The pbix is attached.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
You can create two slicer tables as:
Is_Purchased =
VALUES('Table'[Product Name])
Not_Purchased =
VALUES('Table'[Product Name])
Create measure as:
Measure =
IF(
CONTAINSSTRING(MAX('Table'[Products Concatenated]),ALLSELECTED(Is_Purchased)),
IF(
CONTAINSSTRING(MAX('Table'[Products Concatenated]),ALLSELECTED(Not_Purchased)),
0,
1),
0
)
Here is the output:
The pbix is attached.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
@nora_tolgyesi , Try a measure like
countx(filter(summarize(Table, Table[Customer Name],"_1" ,calculate(countrows(Table),filter(Table[product] ="Product X")),"_1" ,calculate(countrows(Table),filter(Table[product] ="Product Y"))), not(isblank(_1)) && isblank(_2)),[Customer Name])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |