How to rank by date within year and term

[handwepg]

I'm trying to determine from my LargeSurvey table within Term (Fall/spring) and academic year (AcadYr) which survey was administered first, second, etc.

My data looks like this:

Title	StartDate	Term	AcadYr	Population	Index
asdfas	10/12/2017	Fall	17-18	Student	0
oioiu	3/12/2019	Sp	18-19	Student	1
reewes	4/1/2019	Sp	18-19	Faculty	2
sdfasdf	8/25/2017	Fall	17-18	Student	3
werweg	2/2/2018	Sp	17-18	Faculty	4
zxcvz	9/13/2018	Fall	18-19	Student	5

I've created the measure RANK using the following code that I picked up from similar forums.  [Note, I'm not really sure why I created an Index, but the code I found online recommended, so I added it.]

RANK =

MINX (

    FILTER (

        SELECTCOLUMNS (

           ALLSELECTED ( LargeSurvey ),

           "index", LargeSurvey[Index],

           "rank", RANKX ( ALLSELECTED ( LargeSurvey ), LargeSurvey[StartDate],, ASC, DENSE )

        ),

        [index] = MAX ( LargeSurvey[Index] )

    ),

    [rank]

)

The code produced:

Title	StartDate	Term	AcadYr	Population	Index	RANK
asdfas	10/12/2017	Fall	17-18	Student	0	1
sdfasdf	8/25/2017	Fall	17-18	Student	3	2
zxcvz	9/13/2018	Fall	18-19	Student	5	3
oioiu	3/12/2019	Sp	18-19	Student	1	4
reewes	4/1/2019	Sp	18-19	Faculty	2	5
werweg	2/2/2018	Sp	17-18	Faculty	4	6

But what I'd like is the following, so I could, if I wanted to, filter by Population.

Title	StartDate	Term	AcadYr	Population	Index	RANK
asdfas	10/12/2017	Fall	17-18	Student	1	1
sdfasdf	8/25/2017	Fall	17-18	Student	0	2
zxcvz	9/13/2018	Fall	18-19	Student	3	3
werweg	2/2/2018	Sp	17-18	Faculty	2	1
oioiu	3/12/2019	Sp	18-19	Student	4	2
reewes	4/1/2019	Sp	18-19	Faculty	5	1

Thanks

[handwepg]

I had mistakes in my original posting.  Sorry.  I've reposted below...

I'm trying to determine from my LargeSurvey table within Term (Fall/spring) and academic year (AcadYr) which survey was administered first, second, etc.

My data looks like this:

Title	StartDate	Term	AcadYr	Population	Index
asdfas	10/12/2017	Fall	17-18	Student	0
oioiu	3/12/2019	Sp	18-19	Student	1
reewes	4/1/2019	Sp	18-19	Faculty	2
sdfasdf	8/25/2017	Fall	17-18	Student	3
werweg	2/2/2018	Sp	17-18	Faculty	4
zxcvz	9/13/2018	Fall	18-19	Student	5

I've created the measure RANK using the following code that I picked up from similar forums.  [Note, I'm not really sure why I created an Index, but the code I found online recommended, so I added it.]

RANK =

MINX (

    FILTER (

        SELECTCOLUMNS (

           ALLSELECTED ( LargeSurvey ),

           "index", LargeSurvey[Index],

           "rank", RANKX ( ALLSELECTED ( LargeSurvey ), LargeSurvey[StartDate],, ASC, DENSE )

        ),

        [index] = MAX ( LargeSurvey[Index] )

    ),

    [rank]

)

The code produced:

Title	StartDate	Term	AcadYr	Population	Index	RANK
sdfasdf	8/25/2017	Fall	17-18	Student	3	1
asdfas	10/12/2017	Fall	17-18	Student	0	2
werweg	2/2/2018	Sp	17-18	Faculty	4	3
zxcvz	9/13/2018	Fall	18-19	Student	5	4
oioiu	3/12/2019	Sp	18-19	Student	1	5
reewes	4/1/2019	Sp	18-19	Faculty	2	6

But what I'd like is the following, sorted within term and academic year so I could filter by Population.

Title	StartDate	Term	AcadYr	Population	Index	RANK
sdfasdf	8/25/2017	Fall	17-18	Student	3	1
asdfas	10/12/2017	Fall	17-18	Student	0	2
werweg	2/2/2018	Sp	17-18	Faculty	4	1
zxcvz	9/13/2018	Fall	18-19	Student	5	1
oioiu	3/12/2019	Sp	18-19	Student	1	1
reewes	4/1/2019	Sp	18-19	Faculty	2	2

Thanks

[Anonymous · Accepted solution]

StartDateRank = 
if(
    HASONEVALUE( Table1[Title] ),
    var __currentTitle = VALUES( Table1[Title] )
    var __currentYear = values( Table1[AcadYr] )
    var __currentTerm = values( Table1[Term] )
    return
    RANKX(
        CALCULATETABLE(
            ALLSELECTED( Table1 ),
            Table1[Term] = __currentTerm,
            Table1[AcadYr] = __currentYear
        ),
        CALCULATE( values( Table1[StartDate] ) ),,ASC,Dense
    )
)

The assumption is that Title is a unique identifier in the table. No need for an index column.

Best

Darek

[Anonymous]

Interesting... but the table that you say is correct does not seem to be. The third row has rank 3 but it's a different academic year than the previous ones... The other ranks make no sense, either. You did say you wanted to determine the time succession within the same year and term...

[handwepg]

Thanks for pointing out the mistakes in my original posting.  Sorry.  I've reposted below...

I'm trying to determine from my LargeSurvey table within Term (Fall/spring) and academic year (AcadYr) which survey was administered first, second, etc.

My data looks like this:

Title	StartDate	Term	AcadYr	Population	Index
asdfas	10/12/2017	Fall	17-18	Student	0
oioiu	3/12/2019	Sp	18-19	Student	1
reewes	4/1/2019	Sp	18-19	Faculty	2
sdfasdf	8/25/2017	Fall	17-18	Student	3
werweg	2/2/2018	Sp	17-18	Faculty	4
zxcvz	9/13/2018	Fall	18-19	Student	5

I've created the measure RANK using the following code that I picked up from similar forums.  [Note, I'm not really sure why I created an Index, but the code I found online recommended, so I added it.]

RANK =

MINX (

    FILTER (

        SELECTCOLUMNS (

           ALLSELECTED ( LargeSurvey ),

           "index", LargeSurvey[Index],

           "rank", RANKX ( ALLSELECTED ( LargeSurvey ), LargeSurvey[StartDate],, ASC, DENSE )

        ),

        [index] = MAX ( LargeSurvey[Index] )

    ),

    [rank]

)

The code produced:

Title	StartDate	Term	AcadYr	Population	Index	RANK
sdfasdf	8/25/2017	Fall	17-18	Student	3	1
asdfas	10/12/2017	Fall	17-18	Student	0	2
werweg	2/2/2018	Sp	17-18	Faculty	4	3
zxcvz	9/13/2018	Fall	18-19	Student	5	4
oioiu	3/12/2019	Sp	18-19	Student	1	5
reewes	4/1/2019	Sp	18-19	Faculty	2	6

But what I'd like is the following, sorted within term and academic year so I could filter by Population.

Title	StartDate	Term	AcadYr	Population	Index	RANK
sdfasdf	8/25/2017	Fall	17-18	Student	3	1
asdfas	10/12/2017	Fall	17-18	Student	0	2
werweg	2/2/2018	Sp	17-18	Faculty	4	1
zxcvz	9/13/2018	Fall	18-19	Student	5	1
oioiu	3/12/2019	Sp	18-19	Student	1	1
reewes	4/1/2019	Sp	18-19	Faculty	2	2

Thanks