Adding additional criteria to RANKX

[Anonymous]

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:

StudentID	ChangeDate	AcademicYear	Rank
Student1	01/10/2020	2019/20	1
Student1	02/10/2020	2019/20	2
Student1	09/10/2020	2019/20	3
Student2	02/10/2020	2019/20	1
Student2	11/10/2020	2019/20	2
Student2	12/10/2020	2019/20	3
Student2	13/10/2020	2020/21	4
Student3	07/10/2020	2019/20	1
Student3	11/10/2020	2019/20	2
Student3	16/10/2020	2020/21	3
Student4	07/10/2020	2019/20	1
Student4	08/10/2020	2019/20	2

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):

StudentID	ChangeDate	AcademicYear	Rank	NEWRank
Student1	01/10/2020	2019/20	1	1
Student1	02/10/2020	2019/20	2	2
Student1	09/10/2020	2019/20	3	3
Student2	02/10/2020	2019/20	1	1
Student2	11/10/2020	2019/20	2	2
Student2	12/10/2020	2019/20	3	3
Student2	13/10/2020	2020/21	4	1
Student3	07/10/2020	2019/20	1	1
Student3	11/10/2020	2019/20	2	2
Student3	16/10/2020	2020/21	3	1
Student4	07/10/2020	2019/20	1	1
Student4	08/10/2020	2019/20	2	2

Any suggestions as to how to achieve this please?  Many thanks

[edhans]

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

[AlB · Accepted solution]

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]

@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.⚡