Forum Discussion
RANK with PARTITION BY Like SQL
The newer RANK function should make this easier than it used to be.
Try this:
RANK ( DENSE, ORDERBY ( 'Table'[Date], ASC ), PARTITIONBY ( 'Table'[ID] ) )Calculate column on Table, where you add a column to calculate the year
Year = YEAR ( Tabella[Date] )
Finale Column
RANK ( DENSE, ORDERBY( Tabella[Date] ), DEFAULT, PARTITIONBY( Tabella[YEAR]))If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
I do not understand the following
1 are yo ulooking for a column to add to the table or to a measure?
2 your desred result is confusing
| ID | Date | Rank |
| 1 | 01-Jan-21 | 1 |
| 1 | 01-Jan-21 | 1 |
| 1 | 02-Jan-21 | 2 |
| 1 | 03-Jan-21 | 3 |
| 2 | 01-Jan-21 | 1 |
| 2 | 02-Jan-21 | 2 |
| 3 | 01-Jan-00 | 1 |
| 4 | 01-Jan-00 | 1 |
do you want the rank within a year?
Thanks
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Hi mate,
A new column, which ranks the dates grouped by each ID.
The actual date value is irrelevant, I just want the earliest to be 1 for each ID, and the second 2, and so forth.
Calculate column on Table, where you add a column to calculate the year
Year = YEAR ( Tabella[Date] )
Finale Column
RANK ( DENSE, ORDERBY( Tabella[Date] ), DEFAULT, PARTITIONBY( Tabella[YEAR]))If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your threadconsider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI