cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
thongda Frequent Visitor
Frequent Visitor

How to find Customers who have made 0 orders

Hi, I have a scenario like this

There are 8 tables which have relationships with each other as you can see in the image below.

 

As you can see, table Users to table Orders is one-to-many, which means that if new users are added to Users table and they have not purchased any items since the first time they registered their account, the number of orders they have made suppose to be NULL or 0.

What if I want to find those people to see why they registered but did not buy anything.

 

Expected results

1 ACCEPTED SOLUTION

Accepted Solutions
sreenathv Member
Member

Re: How to find Customers who have made 0 orders

You may add a calculated column to your "Users" table using the following expression.

 

OrderLineItems = COUNTROWS(RELATEDTABLE(Orders))=0

This counts the number of records in the Order table for each customer and compares it with 0. If it's zero, it returns TRUE or else FALSE.

 

If you further filter the TRUE values, that's the result you want.

View solution in original post

5 REPLIES 5
AClerk New Contributor
New Contributor

Re: How to find Customers who have made 0 orders

Did you try to apply a filter? Orders = 0

Not sure what you need, but it seems to me straight forward.

thongda Frequent Visitor
Frequent Visitor

Re: How to find Customers who have made 0 orders

Hi @AClerk ,

Thank you for your answer.

 

The tricky part here is that when visualizing Users.Email with Total Number of Orders, it seems like Power BI has applied inner join instead of left join (from table Users to Orders). Therefore, by taking all values in Users table, Power BI only took Users that have User ID in Orders Table which also means that only Users that have made the purchase will be shown in the Result.

As you can see from the image below, I have taken the value email from Users table and Count Order ID from Orders table. After getting the result, I have sorted by Count of order_id and the least order number is 1 (not 0). Thus, when applying filter where Orders = 0, apparently there would be no value.

 

sreenathv Member
Member

Re: How to find Customers who have made 0 orders

You may add a calculated column to your "Users" table using the following expression.

 

OrderLineItems = COUNTROWS(RELATEDTABLE(Orders))=0

This counts the number of records in the Order table for each customer and compares it with 0. If it's zero, it returns TRUE or else FALSE.

 

If you further filter the TRUE values, that's the result you want.

sreenathv Member
Member

Re: How to find Customers who have made 0 orders

You may add a calculated column to your "Users" table using the following expression.

 

OrderLineItems = COUNTROWS(RELATEDTABLE(Orders))=0

This counts the number of records in the Order table for each customer and compares it with 0. If it's zero, it returns TRUE or else FALSE.

 

If you further filter the TRUE values, that's the result you want.

View solution in original post

thongda Frequent Visitor
Frequent Visitor

Re: How to find Customers who have made 0 orders

Thanks for your answer, it works like a charm 🙂

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 343 members 2,987 guests
Please welcome our newest community members: