How to find Customers who have made 0 orders

[Anonymous]

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

[Anonymous]

Did you try to apply a filter? Orders = 0

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

[Anonymous]

Hi @Anonymous ,

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.

[Anonymous · Accepted solution]

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.

[Anonymous]

Thanks for your answer, it works like a charm 🙂

[Anonymous]

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.