topic Re: Know how many users news repeat an order in DAX Commands and Tips

Know how many users news repeat an order

ATO23 — Tue, 24 Oct 2023 09:21:08 GMT

Good morning everyone,

I'm facing a problem in my power BI model and I'm looking for some help.

I have a customer table where I have all the customer ID with every transaction (order) they made during a period. So I have one line per transaction. I also have a column to know if is the first time a customer is purchasing with a boolean of Y/N.

The problem I'm facing is that if I want to know the NEW customers I have in my table, I just need to do a calculate with a filter = Y in the column where I notice if it's the first purchase; however, I want to know how many times a NEW customer bought me in a filter date selection.

For example, a customer buys for the first time in May 2023, and makes another purchase in July 2023. I want to know, that user is new and he did 2 purchases in that period. If I apply a filter in the page to know all the users news, I miss that second purchase that have a N in the first purchase column, and I don't want to lose it.

Any ideas?

Kind regards

Re: Know how many users news repeat an order

some_bih — Tue, 24 Oct 2023 09:42:26 GMT

Hi ATO23 check content on the link for start

https://www.daxpatterns.com/new-and-returning-customers/

Re: Know how many users news repeat an order

EylesIT — Tue, 24 Oct 2023 19:49:36 GMT

ATO23 , here is my suggested solution. Instead of a page-level filter, create the measures needed which do the filtering required.

I have used this set of test data:

CustomerID	orderID	period	order_value	new_customer
C001	O001	01/05/2023	1234	Y
C001	O002	01/07/2023	2345	N
C002	O003	01/05/2023	3274	Y
C003	O004	01/07/2023	2874	Y
C004	O005	01/07/2023	2774	N

Create a measure called [New Customers]:

New Customers = CALCULATE(DISTINCTCOUNT(YourTable[CustomerID]), YourTable[new_customer] = "Y")

Create a measure called [New Customer Orders]:

New Customer Orders = VAR cust = SUMMARIZE( FILTER(YourTable, YourTable[new_customer] = "Y"), YourTable[CustomerID] ) VAR orders = CALCULATE( COUNTROWS(YourTable), ALL(YourTable), YourTable[CustomerID] IN cust ) RETURN orders

Add a slicer to your report, using the [period] field.

Add a Card visual to your report, and drag in the [New Customers] measure.

Add a Card visual to your report, and drag in the [New Customer Orders] measure.

This gives me the following results when May-23 is selected.

There are 2 new customers in May-23 (C001 and C002).

Of those two new customers, there are 3 total orders in the table (O001, 002 and O003).

This gives me the following results when Jul-23 is selected.

There is 1 new customers in Jul-23 (C003).

Of that new customer, there is 1 total orders in the table (O004).

Hope this helps.

Re: Know how many users news repeat an order

ATO23 — Thu, 26 Oct 2023 14:24:25 GMT

That works perfectly! Many thanks for your help!