Forum Discussion

reinholz's avatar
reinholz
Frequent Visitor
9 years ago
Solved

Rank within double grouping - possible?

Hey,   I'm trying to add a rank column to a fact table that ranks based on two groupings. My table basically contains records for user activities with user id, created date and a column with the p...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Hi reinholz

     

    If I've understood you correctly, you want a calculated column that gives you the current row's [created at] rank, among all rows with the same [user id] and [product id].

     

    With DAX, you can do that with an expression like this (you may want to change ASC to DESC):

     

    Rank =
RANKX (
    CALCULATETABLE (
        FactTable,
        ALLEXCEPT ( FactTable, FactTable[user id], FactTable[product id] )
    ),
    FactTable[created at],
    ,
    ASC
)
OwenAuger's avatar
OwenAuger
Super User
9 years ago

Hi reinholz

 

If I've understood you correctly, you want a calculated column that gives you the current row's [created at] rank, among all rows with the same [user id] and [product id].

 

With DAX, you can do that with an expression like this (you may want to change ASC to DESC):

 

Rank =
RANKX (
    CALCULATETABLE (
        FactTable,
        ALLEXCEPT ( FactTable, FactTable[user id], FactTable[product id] )
    ),
    FactTable[created at],
    ,
    ASC
)
reinholz's avatar
reinholz
Frequent Visitor
9 years ago

Thank you! Works exactly as expected!