Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 exam...
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    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

OwenAuger's avatar
OwenAuger
Super User
4 years ago

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's avatar
Anonymous
Not applicable
4 years ago

Thanks OwenAuger, this worked.