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
Icon for Super User rankSuper 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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi aowen,

     

    The solution works great  for static content.

     

    However,  I cannot use Calculated Columns as there are 4 other fileds and a calendar field that are used for filtering data (dynamic filters) on the report.

     

    Also, I need exactly 10 entries per group.

     

    For example, in the above scenario

    for PRODUCT 1, there shall be 10 customers ordered by Sales Amount,

    for PRODUCT 2, there shall be 10 Customers ..etc

     

    I tried with the below DAX expressions along with another field like country ..etc in a matrix and applied.

     

    But as in snapshot, I m seeing incorrect values

    The rank values are coming up from 2 instead of 1

     

    What am I doing wrong?

     

    //Rank decimal values

    Rank over Rank of Customers = IF (HASONEVALUE( FACT_Mail[Sender]),
    RANKX(ALL(FACT_Mail[Sender]), [Rank of Customers by Request Received], ,DESC,DENSE) , BLANK())

     

    //Measure to generate decimal value by taking into account Primary and Secondary Measure to avoid ties

    Rank of Customers by Request Received =

    IF (HASONEVALUE(FACT_Mail[Sender]),

    RANKX(ALL(FACT_Mail[Sender]), [Total Received Requests], ,DESC,DENSE)
    + DIVIDE (
    RANKX ( ALL ( FACT_Mail ), [Total Mail Size]+0,, DESC, Dense ),
    ( COUNTROWS ( ALL ( FACT_Mail ) ) + 1 )),
    BLANK())

     

    //Primary Measure

    Total Received Requests = CALCULATE(COUNT(FACT_Mail[RowId]), USERELATIONSHIP(DIM_DateTable[DateKey], FACT_Mail[DateKey_DateTimeReceived]) )

     

    //Secondary Measure

    Total Mail Size = DIVIDE(CALCULATE(SUM(FACT_Mail[Size]), USERELATIONSHIP(DIM_DateTable[DateKey], FACT_Mail[DateKey_DateTimeReceived]) ), 1024, 0)

     

     

    Thanks,

    Mannu