Forum Discussion
RANK with PARTITION BY Like SQL
Hi,
I am looking to use a DAX formula, similar to the RANK with Partition By window function within SQL.
My desired result is the below:
| 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 |
This DAX is not working, I am receiving all 1 values as below:
| ID | Date | Rank |
| 1 | 01-Jan-21 | 1 |
| 1 | 01-Jan-21 | 1 |
| 1 | 02-Jan-21 | 1 |
| 1 | 03-Jan-21 | 1 |
| 2 | 01-Jan-21 | 1 |
| 2 | 02-Jan-21 | 1 |
| 3 | 01-Jan-00 | 1 |
| 4 | 01-Jan-00 | 1 |
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
8 Replies
The newer RANK function should make this easier than it used to be.
Try this:
RANK ( DENSE, ORDERBY ( 'Table'[Date], ASC ), PARTITIONBY ( 'Table'[ID] ) )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 Anonymous ,
Check this post.
Same issue, utilised exactly, still receiving all 1 values.
rn =VAR CurrentDate =SELECTEDVALUE ( Table[Date] )VAR RankingTable =CALCULATETABLE (SUMMARIZE ( Table, Table[Date] ),ALLSELECTED (), -- filter context of visualVALUES ( Table[ID]) -- retain current Category filter)RETURNRANKX (RankingTable,Table[Date],CurrentDate,ASC)
