Forum Discussion
Duplicate rows cummulative
I have a dataset that's contains duplicates rows, that i need to maintain for some analysis.
My table is something like this:
| CONT | LOTE | DATE | CONT/LOTE |
| 10009 | 10126 | 07/07/2022 | 10009-10126 |
| 10009 | 10126 | 26/05/2022 | 10009-10126 |
| 10009 | 10126 | 19/04/2022 | 10009-10126 |
| 10009 | 10126 | 10/05/2022 | 10009-10126 |
| 10014 | 10138 | 15/07/2022 | 10014-10138 |
| 10014 | 10138 | 20/04/2022 | 10014-10138 |
| 10014 | 10138 | 02/06/2022 | 10014-10138 |
And i need to add a suffix on duplicateds rows, ordered by Date. Something like this:
| CONT | LOTE | DATE | CONT/LOTE | DUPLICATED |
| 10009 | 10126 | 07/07/2022 | 10009-10126 | 4 |
| 10009 | 10126 | 26/05/2022 | 10009-10126 | 3 |
| 10009 | 10126 | 19/04/2022 | 10009-10126 | 1 |
| 10009 | 10126 | 10/05/2022 | 10009-10126 | 2 |
| 10014 | 10138 | 15/07/2022 | 10014-10138 | 3 |
| 10014 | 10138 | 20/04/2022 | 10014-10138 | 1 |
| 10014 | 10138 | 02/06/2022 | 10014-10138 | 2 |
I've already tried this column, but unsuccessful...
Column = CALCULATE (DISTINCTCOUNT ( Table1[Cont/Lote] ),
FILTER (Table1,
Table1[Date] = EARLIER ( Table1[Date] )
&& Table1[Cont/Lote] < EARLIER ( Table1[Cont/Lote] )))
+ 1
Based on your output, it seems you are looking for is Row Number by Group.
Row Number by Group - Static Column = RANKX( Filter('Table', [CONT/LOTE] = Earlier('Table'[CONT/LOTE]) ) , 'Table'[DATE], , ASC, dense)It took me a while to understand, as I was trying to correct the DAX.
Tip: To post sample data, do copy and paste from Excel.
2 Replies
Based on your output, it seems you are looking for is Row Number by Group.
Row Number by Group - Static Column = RANKX( Filter('Table', [CONT/LOTE] = Earlier('Table'[CONT/LOTE]) ) , 'Table'[DATE], , ASC, dense)It took me a while to understand, as I was trying to correct the DAX.
Tip: To post sample data, do copy and paste from Excel.Hi,
This calculated column formula works
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CONT]=EARLIER(Data[CONT])&&Data[DATE]<=EARLIER(Data[DATE])))Hope this helps.
