Forum Discussion
joshua1990
Post Prodigy
Post ProdigyRANK by DateTime Column
Hi experts!
How is it possible to get a rank column based on a DateTime column?
Hi,
For duplicates we can modify the last optional parameter of RANKX e.g.
Dense:Skip:
In addition to this we can add an index column to the table and use that as a secondary ranking:
Dax:
Column =var _dateR =RANKX('Table (15)',value('Table (15)'[Column1]),'Table (15)'[Column1],ASC,Dense) returnvar _indexR =RANKX('Table (15)',value('Table (15)'[Index]),'Table (15)'[Index],ASC,Dense) return
_dateR+_indexR
For ranking column add this:
Rank = RANKX('Table (15)',VALUE('Table (15)'[Column]),,DESC)
End result (change DESC to ASC to match your use case):
ValtteriN
Community Champion
Community ChampionHi,
Here is one way to do this:
Column = RANKX('Calendar',value('Calendar'[Date]),'Calendar'[Date],DESC)
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!
My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
joshua1990Post Prodigy
Post ProdigyValtteriN : Thanks a lot! Does this work with duplicates? It seems like not. How can we add this aspect into the function?
- ValtteriN
Hi,
For duplicates we can modify the last optional parameter of RANKX e.g.
Dense:Skip:
In addition to this we can add an index column to the table and use that as a secondary ranking:
Dax:
Column =var _dateR =RANKX('Table (15)',value('Table (15)'[Column1]),'Table (15)'[Column1],ASC,Dense) returnvar _indexR =RANKX('Table (15)',value('Table (15)'[Index]),'Table (15)'[Index],ASC,Dense) return
_dateR+_indexR
For ranking column add this:
Rank = RANKX('Table (15)',VALUE('Table (15)'[Column]),,DESC)
End result (change DESC to ASC to match your use case):