Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Adding additional criteria to RANKX

I have a column which ranks by student code and date change using the following:

 

Rank = RANKX( FILTER( 'Table', [StudentID] = EARLIER( [StudentID] )), [ChangeDate] , , ASC, DENSE)
 
which gives me the following table:
StudentIDChangeDateAcademicYearRank
Student101/10/20202019/201
Student102/10/20202019/202
Student109/10/20202019/203
Student202/10/20202019/201
Student211/10/20202019/202
Student212/10/20202019/203
Student213/10/20202020/214
Student307/10/20202019/201
Student311/10/20202019/202
Student316/10/20202020/213
Student407/10/20202019/201
Student408/10/20202019/202

 

What I would like is to add additional criteria in order to restart the ranking wherever there is a change of AcademicYear for each student, so the table would look as follows (changes highlighted in red):

StudentIDChangeDateAcademicYearRankNEWRank
Student101/10/20202019/2011
Student102/10/20202019/2022
Student109/10/20202019/2033
Student202/10/20202019/2011
Student211/10/20202019/2022
Student212/10/20202019/2033
Student213/10/20202020/2141
Student307/10/20202019/2011
Student311/10/20202019/2022
Student316/10/20202020/2131
Student407/10/20202019/2011
Student408/10/20202019/2022
 
Any suggestions as to how to achieve this please?  Many thanks
Need Help
  • AlB's avatar
    AlB
    5 years ago

    Hi Anonymous 

    Rank V2 =
RANKX (
    FILTER (
        Table1,
        Table1[StudentID] = EARLIER ( Table1[StudentID] )
            && Table1[AcademicYear] = EARLIER ( Table1[AcademicYear] )
    ),
    Table1[ChangeDate],
    ,
    ASC,
    DENSE
)

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    Rank V2 =
RANKX (
    FILTER (
        Table1,
        Table1[StudentID] = EARLIER ( Table1[StudentID] )
            && Table1[AcademicYear] = EARLIER ( Table1[AcademicYear] )
    ),
    Table1[ChangeDate],
    ,
    ASC,
    DENSE
)

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    Anonymous try this

     

    Rank  = RANKX( FILTER( 'Rank', 'Rank'[StudentID] = EARLIER( 'Rank'[StudentID] ) && 'Rank'[AcademicYear] = EARLIER('Rank'[AcademicYear])), 'Rank'[ChangeDate] , , ASC, DENSE)

     

    Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

    I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

    ⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    The following measure will do it Anonymous I believe.

     

     

    New Rank =
VAR varStudentID =
    MAX( 'Table'[StudentID] )
VAR varAcademicYear =
    MAX( 'Table'[AcademicYear] )
VAR Result =
    RANKX(
        FILTER(
            ALL( 'Table' ),
            'Table'[StudentID] = varStudentID
                && 'Table'[AcademicYear] = varAcademicYear
        ),
        CALCULATE(
            SELECTEDVALUE( 'Table'[ChangeDate] )
        ),
        ,
        ASC,
        DENSE
    )
RETURN
    Result