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.
Hi Power BI Gurus, I am in need of your expertise one more time. I am trying to find out customers who bought only one product. I have a dimension model: Customer, Product and Sales and I want to acheive this using these 3 tables. We use tabular model, which means I can't add columns in Power BI and measures functionality is also limited.
Here is my sample data:
CustKey | CustName | ProdKey | ProdName | CustKey | Order Id | ProductKey | ||
123 | Iron Man | 100 | iPhone X | 123 | 1 | 100 | ||
53 | Thor | 110 | iPod | 123 | 1 | 110 | ||
2442 | Capitan America | 120 | Apple Watch | 123 | 1 | 120 | ||
782 | Spider Man | 130 | iPhone 8s | 53 | 2 | 130 | ||
428 | Dr. Strange | 140 | Airport | 53 | 2 | 140 | ||
150 | Apple TV | 2442 | 3 | 120 | ||||
160 | Macbook Air | 782 | 4 | 150 | ||||
782 | 4 | 160 | ||||||
428 | 5 | 120 | ||||||
53 | 6 | 120 |
Output:
Customer who only bought Apple Watch:
Customer Name
Capitan America
Dr. Strange
Customer who only bought Macbook:
Customer Name
Spider Man
I did ask something similar earlier but that was using one single table and when I tried to apply the same concept using multiple tables(AKA dimension model), I am not successful 😞
FYI-Link of the previous post :https://community.powerbi.com/t5/Desktop/Customers-who-bought-a-SPECIFIC-product-only-but-not-others...
Appreciate all the help and contrbution to the PBI community.
PS: I was wondering whether Microsoft can come up with a DAX function to achieve something like this. May be an idea worth putting up on ideas.powerbi.com...
Best,
SN
Solved! Go to Solution.
Hi @Anonymous
I believe this should be as simple as this:
CALCULATE( COUNTROWS( Orders ), ALL( Products ) ) = COUNTROWS( Orders )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Anonymous
looks like you need to add an extra condition. Did not test it but should be something like the below:
IF(
NOT ISEMPTY( CALCULATETABLE( Orders, ALL( Products) ) ),
CALCULATE( COUNTROWS( Orders ), ALL( Products ) ) = COUNTROWS( Orders )
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@LivioLanzoThanks so much !!! This is exactly what I wanted. Appreciate your help and sorry for the delay in responding to your solution.
Hi,
I had contributed to that post in September. Did you try out my solution?
Hi @Anonymous
I believe this should be as simple as this:
CALCULATE( COUNTROWS( Orders ), ALL( Products ) ) = COUNTROWS( Orders )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo
Sorry for the delayed response. This solution is exactly what I wanted. However, when I applied this to our actual data, I am finding an issue. If I were to translate my issue to this sample data, we have customers who didn't place any orders yet. And for such customers the flag will be always be true. I want to exclude such customers in this measure.
Lets say I have the below two customers(who didn't placed any order yet)
999 Bruce Lee
111 Jackie Chan
They will show up as True irrespective of what product I pick on the filter.
Thanks so much for your help.
Hi @Anonymous
looks like you need to add an extra condition. Did not test it but should be something like the below:
IF(
NOT ISEMPTY( CALCULATETABLE( Orders, ALL( Products) ) ),
CALCULATE( COUNTROWS( Orders ), ALL( Products ) ) = COUNTROWS( Orders )
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@LivioLanzoThanks so much !!! This is exactly what I wanted. Appreciate your help and sorry for the delay in responding to your solution.
Sorry for the delayed response. This solution is exactly what I wanted. However, when I applied this to our actual data, I am finding an issue. If I were to translate my issue to this sample data, we have customers who didn't place any orders yet. And for such customers the flag will be always be true. I want to exclude such customers in this measure.
Lets say I have the below two customers(who didn't placed any order yet)
999 Bruce Lee
111 Jackie Chan
They will show up as True irrespective of what product I pick on the filter.
Thanks so much for your help.
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |