1

I need to build a formula in DAX that will show the number of customers who purchased again after their initial purchase, broken out by Product. I have a standard data warehouse with a Order Placed fact table, a Customer dimension table, and a Product dimension table. I am able to find the number of customers who purchased each product on their initial purchase by using this formula:

First Purchase Customer Count = CALCULATE(DISTINCTCOUNT(Demand[CustomerKey]),Demand[Customer Order Sequence Number] = 1)

My visual is a table with the Product as the only attribute, so this first formula is being calculated per Product. The next formula needs to count the number of customers who bought a second time, regardless of what Product they purchased that second time, but it should only include customers who purchased the current Product the first time. I have successfully created this formula to do it, but it usually errors out on the 1M row limit unless I filter the Product by subcategory.

Rebuyer Count = COUNTROWS(INTERSECT(SUMMARIZE(FILTER(Demand,Demand[Customer Order Sequence Number] = 1),[CustomerKey]),SUMMARIZE(CALCULATETABLE(FILTER(Demand,Demand[Customer Order Sequence Number] = 2),all('Product')),[CustomerKey])))

How can I improve this formula so it will run without bombing?

Improve this question

1 Answer 1

Reset to default
0

Old question, but on the off-chance that this is still an issue for you, have you reviewed the New and Returning Customers pattern on DaxPatterns.com? http://www.daxpatterns.com/new-and-returning-customers/

Your returning customers solution is creating a table of first-time customers, and then a table of second-time customers, and then joining those two tables together to count the number of first-time customers who are also a second-time customer.

The DaxPatterns solution is slightly different. Rather than calculating 2 potentially large tables of customers on the fly and then joining them, it is counting existing customers who have made a prior purchase.

You would need to adapt their solution to meet your requirements. They have a concept of Absolute Returning Customers (meaning the customer's first-purchase was for any product, and their second purchase was for the specific product you care about). You want the reverse of that (the customer's first-purchase was for a specific product, and their second purchase can be anything).

Overall, however, you'll have less bombing out if you start from a single list of first-time customers and filter out the customers you don't want (i.e. who never bought again) vs. compiling 2 separate lists of customers and intersecting them.

(It's the difference between letting everyone into the theatre, then throwing out everyone without a ticket, vs. checking tickets at the door and only letting people with a ticket in).

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.