cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
srikanthnama Regular Visitor
Regular Visitor

Customers who bought only one product

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:

CustKeyCustName ProdKeyProdName CustKeyOrder IdProductKey
123Iron Man 100iPhone X 1231100
53Thor 110iPod 1231110
2442Capitan America 120Apple Watch 1231120
782Spider Man 130iPhone 8s 532130
428Dr. Strange 140Airport 532140
   150Apple TV 24423120
   160Macbook Air 7824150
      7824160
      4285120
      536120

 

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: Customers who bought only one product

Hi @srikanthnama

 

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!  

View solution in original post

LivioLanzo Super Contributor
Super Contributor

Re: Customers who bought only one product

Hi @srikanthnama

 

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!  

View solution in original post

srikanthnama Regular Visitor
Regular Visitor

Re: Customers who bought only one product

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

View solution in original post

6 REPLIES 6
LivioLanzo Super Contributor
Super Contributor

Re: Customers who bought only one product

Hi @srikanthnama

 

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!  

View solution in original post

Super User
Super User

Re: Customers who bought only one product

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
srikanthnama Regular Visitor
Regular Visitor

Re: Customers who bought only one product

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.

 

srikanthnama Regular Visitor
Regular Visitor

Re: Customers who bought only one product

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 Super Contributor
Super Contributor

Re: Customers who bought only one product

Hi @srikanthnama

 

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!  

View solution in original post

srikanthnama Regular Visitor
Regular Visitor

Re: Customers who bought only one product

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

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)