cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
awitt Regular Visitor
Regular Visitor

Unique Value Count

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). 

 

ItemOrder #Customer #Customer Type
Item 11001ABCNew
Item 21001ABC 
Item 31001ABC 
Item 41001ABC 
Item 11002XYZNew
Item 21002XYZ 
Item 31002XYZ 
Item 41002XYZ 
Item 51002XYZ 
Item 11003ABCReturning
Item 21003ABC 
Item 11004LMNONew
Item 21004LMNO 
Item 31004LMNO 
Item 11005XYZReturning
Item 21005XYZ 
Item 31005XYZ 
Item 11006QRSNew
Item 21006QRS 
Item 31006QRS 
Item 41006QRS 
Item 51006QRS 
Item 61006QRS 
1 ACCEPTED SOLUTION

Accepted Solutions
MitchM Member
Member

Re: Unique Value Count

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"
    )

 

 

Example.jpgAre 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.

View solution in original post

4 REPLIES 4
MitchM Member
Member

Re: Unique Value Count

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 Regular Visitor
Regular Visitor

Re: Unique Value Count

No - unfortunately that is showing everything as new. 

 

Capture.PNG

MitchM Member
Member

Re: Unique Value Count

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"
    )

 

 

Example.jpgAre 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.

View solution in original post

awitt Regular Visitor
Regular Visitor

Re: Unique Value Count

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 205 members 2,245 guests
Please welcome our newest community members: