Forum Discussion
DAX - RANKX with Partition By
Hi,
I am trying to create a rankx measure that will create a row number by date but also partitioned by category. This will also dynamically update when the user changes the date filter. For example:
| Category | Date | rn |
| a | 01/01/2022 | 1 |
| a | 02/01/2022 | 2 |
| a | 03/01/2022 | 3 |
| b | 01/01/2022 | 1 |
| c | 02/01/2022 | 1 |
| d | 03/01/2022 | 1 |
If the user then change the date slicer to 02/01/2022 - 03/01/2022 the result would be:
| Category | Date | rn |
| a | 02/01/2022 | 1 |
| a | 03/01/2022 | 2 |
| c | 02/01/2022 | 1 |
| d | 03/01/2022 | 1 |
Any help would be most welcome!
Hi Anonymous
Something like this (replace Table references as necessary):
rn = VAR CurrentDate = SELECTEDVALUE ( YourTable[Date] ) VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( YourTable, YourTable[Date] ), ALLSELECTED (), -- filter context of visual VALUES ( YourTable[Category] ) -- retain current Category filter ) RETURN RANKX ( RankingTable, YourTable[Date], CurrentDate, ASC )Regards,
Owen
6 Replies
Hi Anonymous
Something like this (replace Table references as necessary):
rn = VAR CurrentDate = SELECTEDVALUE ( YourTable[Date] ) VAR RankingTable = CALCULATETABLE ( SUMMARIZE ( YourTable, YourTable[Date] ), ALLSELECTED (), -- filter context of visual VALUES ( YourTable[Category] ) -- retain current Category filter ) RETURN RANKX ( RankingTable, YourTable[Date], CurrentDate, ASC )Regards,
Owen
Hi OwenAuger, after getting the row number I am trying to count the categories where the row number = 1 which I thought would be a simple calculate function:
Count RN =CALCULATE(
COUNT(Table, Table[Category]), FILTER(Table,'_DAX Measures'[_rn] = 1))This doesn't seem to work. Do you know how to get round this?Thanks in advanceI'm thinking something like this, if you want to count the number of times [_rn]=1 in that particular visual, assuming you're placing this as a standalone measure outside the original visual.
Count RN = SUMX ( SUMMARIZE ( Table, Table[Category], Table[Date] ), IF ( [_rn] = 1, 1 ) )
For fun only, the logical is replicated in Excel,
