Forum Discussion
Count previous rows in table with same user ID
Hi,
I'm looking for a way to filter my datasource based on whether a customer is appearing in the table for the first time. A simplified version of the table would look something like this:
| User_ID | Date | Amount |
| 1234 | 01-Apr | 10 |
| 2345 | 01-Apr | 20 |
| 3456 | 02-Apr | 30 |
| 4567 | 02-Apr | 40 |
| 1234 | 03-Apr | 50 |
What I would like is something that looks like this, with the "New" column specifying whether a customer is appearing for the first time or not:
| User_ID | Date | Amount | New |
| 1234 | 01-Apr | 10 | 1 |
| 2345 | 01-Apr | 20 | 1 |
| 3456 | 02-Apr | 30 | 1 |
| 4567 | 02-Apr | 40 | 1 |
| 1234 | 03-Apr | 50 | 0 |
I could quite easily just do a count of the total number of times a customer appears in the table as a proxy, but that would break it as soon as the customer appears a second time. Is there a way to calculate this on the basis of dates? It would be so easy in Excel, but I'm not quite up to speed on DAX yet!
Thanks in advance,
Matt
You can create a Column like this
New Column = IF ( 'Table'[Date] = CALCULATE ( FIRSTDATE ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[User_ID] ) ), 1, 0 )Hope this helps! :smileyhappy:
2 Replies
You can create a Column like this
New Column = IF ( 'Table'[Date] = CALCULATE ( FIRSTDATE ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[User_ID] ) ), 1, 0 )Hope this helps! :smileyhappy:
