Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
Will this measure work?
New/Recurring = VAR EarliestDate = CALCULATE( MIN( Table1[date] ), ALLEXCEPT( Table1,Table1[company] ) ) RETURN IF( SELECTEDVALUE( Table1[date] ) = EarliestDate, "New", "Returning" )
No - unfortunately that is showing everything as new.
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.
I think relating the date table is messing something up. When i use the data within this specifc data, your solution works. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |