If value is a duplicate then "X" else "Y"

[Anonymous]

Hallo everybody this is my first question in this forum.

I want to set up a new column with following content.

I have a column with customer numbers some of these are double which is ok in this case.

Another column displays the year of activity of a customer.

In a new column I want to categorize the customers into three different classes.

First is Existing Customer:  At the moment I solved it in Excel by saying every double customer numbers is a "Existing Customer"

Secondly is New Customer: The customer numbers which are only one time in the column and were active in the current year shall be displayed as "New customer"

Thirdly is One time customer: Customer number only one time in the column and only one activity in all years.

My idea was to solve it in a if formular. The problem is how to implement the duplicates factor.

I am looking forward to your suggestions and ideas. All the best and thank you for your help in advance.

[AnthonyTilley · Accepted solution]

i would do this in each stage 

Column = 
-- get the date and customer number
var d = 'Table'[Date]
var c = 'Table'[CusNO]

--find the total count of the customer id 
var totalcount = CALCULATE(COUNTA('Table'[CusNO]),ALL('Table'),'Table'[CusNO] = c)

--check if customer is one time only
var otc = if(totalcount = 1,true,false)

--find the total year count
var yearcount = CALCULATE(COUNTA('Table'[CusNO]),ALL('Table'),'Table'[CusNO] = c,year('Table'[Date])=year(d))

--if total year count is 1 then new custoemr
var nc1 = if(yearcount = 1 , TRUE(),FALSE())

-- find the date of the first entry in each year 
var fd = CALCULATE(min('Table'[Date]),ALL('Table'),'Table'[CusNO] = c,year('Table'[Date])=year(d))

-- if the date in veriable d is = to the min date then new customer
var nc2 = if(fd = d, TRUE(),FALSE())

-- check against each of the statments above and set a Status
var ret = if(otc,"ONE TIME CUSTOMER",
                if(nc1,"NEW CUSTOMER",
                    if(nc2,"NEW CUSTOMER","EXISTING CUSTOMER")))

return ret

this will give you a status based on the date and customer no colunm 

if only one entry over all then = "ONE TIME CUSTOMER"

if first time that year but has ordered in other years then "NEW CUSTOMER"

if more than second time in the year then "EXISTING CUSTOMER"