cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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 Established Member
Established 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 Established Member
Established 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 Established Member
Established 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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors