Customers who bought only one product

[Anonymous]

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

[LivioLanzo · Accepted solution]

Hi @Anonymous

I believe this should be as simple as this:

CALCULATE( COUNTROWS( Orders ), ALL( Products ) ) = COUNTROWS( Orders )

[LivioLanzo · Accepted solution]

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 )

)

[Ashish_Mathur]

Hi,

I had contributed to that post in September.  Did you try out my solution?

[LivioLanzo · Accepted solution]

Hi @Anonymous

I believe this should be as simple as this:

CALCULATE( COUNTROWS( Orders ), ALL( Products ) ) = COUNTROWS( Orders )

[Anonymous]

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.

[LivioLanzo · Accepted solution]

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 )

)

[Anonymous · Accepted solution]

@LivioLanzoThanks so much !!! This is exactly what I wanted. Appreciate your help and sorry for the delay in responding to your solution.

[Anonymous]

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.