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):
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
Community Champion
Community ChampionHi,
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:
For ranking column add this:
End result (change DESC to ASC to match your use case):
Column =
var _dateR =
RANKX('Table (15)',value('Table (15)'[Column1]),'Table (15)'[Column1],ASC,Dense) return
var _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):