Flagging customers as 'return' if they have more than one order

[user5341567]

Hi all,

I have a table with one or more order_ids per customer_id, and I'm trying to flag customers who have more than one order_id as 'return' customers.

As an example below: I have customer_id and order_id, but I'm trying to populate customer_type.

order_ids 001, 002, 003, 004, and 005 have multiple records of the same customer_id, so I wish to flag that customer as 'Return'. order_id 006 only has one customer_id in the table, so I wish to flag that customer as 'New', etc.

Has anyone tried to do this?

I tried to first count the number of orders per customer, then use another field to say 'if # of orders > 1 then return else new', but my formula returns 1. All of the solutions I have tried gives me 1.

Here's one of the formulas I tried:

# of orders =

CALCULATE (

    DISTINCTCOUNT ( Fact_orders[order_id] ),

    FILTER (

        ALL ( Fact_orders ),

        [customer_id] = MAX ( Fact_orders[customer_id] )

    )

)

Any insight would be greatly appreciated!

[user5341567]

This worked perfectly, thank you for your reply!

[Deku · Accepted solution]

returning = 
var customerID = 'Table (3)'[customerID]
var orders =
CALCULATE(
    DISTINCTCOUNT( 'Table (3)'[orderId] )
    ,REMOVEFILTERS( 'Table (3)'[orderId] )
)
RETURN
IF( orders > 1, "Returning", "New" )