Forum Discussion

STEPHENC's avatar
STEPHENC
Frequent Visitor
8 years ago

RANK value in Calculate Column with Filtered Values

I am new to this forum and need to ask a question related to RANKX function.

 

I have a table containing Multiple Records per Clients [SUBID]. These records each have a [DATE] and a [SERVICEID] and the combination of these three attributes is Unique. I need to create a Calculated Index (Rank?) Column that will Rank each SUBID record by [DATE] (first) and [SERVICEID]. Conceptually this should emulate the SQL :

 

rank() over (partition by SUBID order by DATE ASC, SERVICEID DESC)

 

I also need this Rank to be Calculated based on whatever record filters are enabled which may be daterange or any other column value.

 

Any help would be appreciated as I have been going round in circles for days

Need Help
Tutorial Requests

4 Replies

    • STEPHENC's avatar
      STEPHENC
      Frequent Visitor
      8 years ago


      Hi v-caliao-msft, Thanks for your reply.

       

      My problem is slightly different. My dataset represents transactions showing ServiceID, Date, ClientID and a bunch of other attributes per record. I am not interesetd in ranking by any value but I want to Rank transactions (Rows) per Client by Date. That is each client ID will have its transactions ranked by Date in a calculated column. The following DAX formula in fact does this

       

      RANK =
                VAR
                   TQB =CALCULATETABLE(IBRO_BB,ALLEXCEPT(IBRO_BB,IBRO_BB[CLIENT_ID]))
                RETURN

      RANKX( TQB,IBRO_BB[DATE].[Date],,ASC)

       

      (this is similar to SQL rank() with partitioning by CLIENT_ID and ordering by [Date]  for ALL records)

      ..

       

      The problem is that if I have a FILTER/SLICER on the dates, the rank value does not recalculate only on the FILTERED/SLICED records. The two images below try to illustrate this. Column is the Calculated Column. The first image shows how I would like the records ranked and that works fine.

       

      The second image shows how I would like the RANK to be calculated when I Filter/Slice February out. Column 1 shows how this actually appears and the "Column as Needed" is the result I want. Filtered out records should not be ranked.

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hey STEPHENC,

        Were you able to resolve this issue with dax, or any other method?

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    8 years ago

    STEPHENC,

     

    I have tested it on my local environment, we can use rankx and allexcept function to achieve this requirement.

    Smaple data

    Create two measures.
    TotalAmount = SUM(Table2[Amount])

    Rank = RANKX(ALLEXCEPT(Table2,Table2[SUBID],Table2[DATE]),Table2[TotalAmount])

    Results.

     

    Regards,

    Charlie Liao