Unique Value Count

[awitt]

Looking to create a measure or a column that identifies when a value reoccurs within a data set but isolates the multiple times it shows up in one unique occurance. 

Basically i am looking to return the column (again this could be in a measure) that identifies when a customer # is new. The issue with just doing a distinctcount is that it removes the times the customer reoccurs. By doing a simple count though, it is taking into consideration the # of items that customer bought which I dont want. For example, customer ABC is listed 6 times in this data set but a distinctcount would just give it a value of 1. I want the first time this customer orders, that customer to be considered new, but the second time (there is a date table related as well) that ABC customer to be tagged as a recurring customer. The result of this data would be 6 orders (1001 - 1006) featuring 4 new customer (ABC, XYZ, LMNO, & QRS) and 2 returning customers (ABC & XYZ). 

Item	Order #	Customer #	Customer Type
Item 1	1001	ABC	New
Item 2	1001	ABC
Item 3	1001	ABC
Item 4	1001	ABC
Item 1	1002	XYZ	New
Item 2	1002	XYZ
Item 3	1002	XYZ
Item 4	1002	XYZ
Item 5	1002	XYZ
Item 1	1003	ABC	Returning
Item 2	1003	ABC
Item 1	1004	LMNO	New
Item 2	1004	LMNO
Item 3	1004	LMNO
Item 1	1005	XYZ	Returning
Item 2	1005	XYZ
Item 3	1005	XYZ
Item 1	1006	QRS	New
Item 2	1006	QRS
Item 3	1006	QRS
Item 4	1006	QRS
Item 5	1006	QRS
Item 6	1006	QRS

[MitchM]

Will this measure work?

New/Recurring = 
VAR EarliestDate = 
    CALCULATE(
        MIN( Table1[date] ),
        ALLEXCEPT( Table1,Table1[company] )
    )

RETURN 
    IF(
        SELECTEDVALUE( Table1[date] ) = EarliestDate,
        "New",
        "Returning"
    )

[awitt]

No - unfortunately that is showing everything as new. 

[MitchM · Accepted solution]

I just reproduced your data set and, using this code, see this:

New/Recurring = 
VAR EarliestDate = 
    CALCULATE(
        MIN( Table2[date] ),
        ALLEXCEPT( Table2,Table2[Customer #] )
    )

RETURN 
    IF(
        SELECTEDVALUE( Table2[date] ) = EarliestDate,
        "New",
        "Returning"
    )

Are you using the date column in the table with the rest of the data or a date table? The way the measure works is to find the the unique earliest date for each Customer. Not sure how a date table would impact this.

[awitt]

I think relating the date table is messing something up. When i use the data within this specifc data, your solution works. Thanks!