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
Apologies, I desire this to be ranked by earliest date.
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