Need to identify customer type by customer ID and Order ID

[UserQuestions_]

I have a large dataset with Sales transactions, Customer ID, Order ID and products purchased with time & date. I am needing to classify customers on if they are returning, unknown or new leveraging customer ID and distinct count of order ID. 

If a customer ID = 0, they are unknown

If unique customer ID = 1 unique order ID, they are new

If unique customer ID > 1 unique order ID, they are returning. 

My sales table  is set up:

Customer ID	Order ID	Product ID	Net Sales	Qty	POS Location
735711	12345	1	5	1	online
735711	12346	2	10	1	store 1
735711	12347	3	5	1	store 1
008972	11415	1	5	1	online
0	18791	1	5	1	online

I have tried a calculated column & measure but keep receiving syntax errors. Thank you for your assistance! 

[SamsonTruong · Accepted solution]

Hi @UserQuestions_ ,

Please try the following DAX for a calculated column and let me know if this achieves your desired result:

Customer Type = 
VAR CurrentCustomer = 'Sales'[Customer ID]
VAR DistinctOrderCount = 
    CALCULATE(
        DISTINCTCOUNT('Sales'[Order ID]),
        ALLEXCEPT('Sales', 'Sales'[Customer ID])
    )
RETURN
    SWITCH(
        TRUE(),
        CurrentCustomer = 0, "Unknown",
        DistinctOrderCount = 1, "New",
        DistinctOrderCount > 1, "Returning"
    )

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

Connect with me on LinkedIn

Check out my Blog

Going to the European Microsoft Fabric Community Conference? Check out my Session

[UserQuestions_]

Thank you so much this worked perfectly! 

[SamsonTruong]

Awesome, glad to hear it worked!