Forum Discussion

saranee's avatar
saranee
Icon for Helper I rankHelper I
8 years ago
Solved

Dynamic ranking based on date

Hi all,   Request your help on this.   We are basically having a table with Name,Date and based on date we are assigning Rank. We have calculated column to calculate rank:RANKX(FILTER(Table1,Tab...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    saranee

     

    Try this MEASURE for dynamic RANKING

     

    RANK =
RANKX (
    FILTER ( ALLSELECTED ( Table1 ), Table1[Name] = SELECTEDVALUE ( Table1[Name] ) ),
    CALCULATE ( SELECTEDVALUE ( Table1[Date] ) ),
    ,
    DESC,
    DENSE
)
ricardocamargos's avatar
ricardocamargos
Icon for Continued Contributor rankContinued Contributor
8 years ago

Hi saranee,

 

Why don't u use measure instead of calculated column ?

 

Ricardo

saranee's avatar
saranee
Icon for Helper I rankHelper I
8 years ago

HI Richard,

 

We will be then unable to use EARLIER in measure.Please can you suggest some alternative as we want seperate set of names and then trying to find rank.

 

Thanks,

saranee

  • ricardocamargos's avatar
    ricardocamargos
    Icon for Continued Contributor rankContinued Contributor
    8 years ago

    Hi saranee,

     

    Try this code:

     

    _Index =
    IF(ISBLANK(CALCULATE(COUNTROWS(Table2); FILTER(ALLSELECTED(Table2[Date]); Table2[Date] < MAX(Table2[Date]))));
    1;
    CALCULATE(COUNTROWS(Table2); FILTER(ALLEXCEPT(Table2; Table2[Name]); Table2[Date] < MAX(Table2[Date]))) + 1)

     

    Ricardo

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion
      8 years ago

      saranee

       

      Try this MEASURE for dynamic RANKING

       

      RANK =
RANKX (
    FILTER ( ALLSELECTED ( Table1 ), Table1[Name] = SELECTEDVALUE ( Table1[Name] ) ),
    CALCULATE ( SELECTEDVALUE ( Table1[Date] ) ),
    ,
    DESC,
    DENSE
)