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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
OwenAuger
Icon for Super User rankSuper User
4 years ago

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