Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi there,
I have this data set where each patient goes through different treatments (each row is a new treatment).
I would like to add a new column (using DAX) that creates a sequence of numbers from 1 to "N" for each Patient, being 1 assigned to the earliest date.
This is what the new column should look like:
Patient | Created on (d/mm/yyyy) | Treatment number (calculated column) |
Patient A | 16/08/2023 | 3 |
Patient A | 5/05/2023 | 2 |
Patient A | 25/03/2023 | 1 |
Patient B | 13/08/2023 | 2 |
Patient B | 5/05/2023 | 1 |
Patient C | 19/06/2023 | 1 |
Patient D | 8/09/2023 | 2 |
Patient D | 14/07/2023 | 1 |
Thanks!
Solved! Go to Solution.
Try the following formula in a calculated column:
Treatment Number = COUNTROWS(FILTER('Table', 'Table'[ Created on (d/mm/yyyy)] <= EARLIER('Table'[ Created on (d/mm/yyyy)]) && 'Table'[Patient] = EARLIER('Table'[Patient])))
It's working perfectly. Thanks, Vicky!
Try the following formula in a calculated column:
Treatment Number = COUNTROWS(FILTER('Table', 'Table'[ Created on (d/mm/yyyy)] <= EARLIER('Table'[ Created on (d/mm/yyyy)]) && 'Table'[Patient] = EARLIER('Table'[Patient])))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
21 | |
17 | |
16 | |
11 | |
7 |
User | Count |
---|---|
26 | |
25 | |
12 | |
12 | |
12 |