Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
Anonymous
Not applicable

Did you try to apply a filter? Orders = 0

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

Anonymous
Not applicable

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.

 

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
Not applicable

Thanks for your answer, it works like a charm 🙂

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.