DAX - RANKX with Partition By

[Anonymous]

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!

[CNENFRNL]

For fun only, the logical is replicated in Excel,

[Anonymous]

Thanks CNENFRNL, this worked also.

[OwenAuger · Accepted solution]

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

[Anonymous]

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 advance

[OwenAuger]

I'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 )
)

[Anonymous]

Thanks OwenAuger, this worked.