Creating a measure to return the max value from a column for a value from another measure

[PBOBOP]

Hello, 

I would like to create a table like below to reflect students' test information.
Every day that a student takes a test, they have to take 2 tests on that day. Only the test which they scored the highest for will be considered.

For example, in the table below, student 319603 last took 2 tests on 21/12/2023. The highest result he achieved out of the 2 tests was 70. Prior to 21/12/2023, the last time he took 2 tests was 18/12/2023. I am having trouble getting the higher result of the 2 tests that he sat for on that day. 

Currently the measures I am using are:

Latest Test = MAX('Students Tests'[Test Date])

Result Latest Test =

CALCULATE(

    MAXX(

        KEEPFILTERS(VALUES('Students Tests'[Test Date])),

        CALCULATE(MAXX('Students Tests', 'Students Tests'[Results]))))

Second Latest Test =

VAR LatestTest = [Latest Test]

RETURN

    CALCULATE(

        MAX('Students Tests'[Test Date]),

        'Students Tests'[Test Date] < LatestTest

    )

Result Second Latest Test =

VAR SecondLatestTest = [Second Latest Test]

RETURN

CALCULATE(

    MAX('Students Tests'[Results]),

        FILTER(ALL('Students Tests'[Test Date]),'Students Tests'[Test Date])=SecondLatestTest)

However, the following error message shows:

Here is some sample data:

Student ID	Test Date	Results	Test Number
14539	6/01/2020	100	2
14539	6/01/2020	80	1
14539	9/01/2020	100	2
14539	9/01/2020	80	1
14539	16/01/2020	80	1
14539	16/01/2020	100	2
14554	9/03/2020	80	2
14554	9/03/2020	80	1
14554	12/03/2020	100	2
14554	12/03/2020	80	1
14554	16/03/2020	100	2
14554	16/03/2020	80	1
15503	6/01/2020	80	1
15503	6/01/2020	80	2
15503	9/01/2020	100	1
15503	9/01/2020	100	2
15503	16/01/2020	100	2
15503	16/01/2020	80	1
15503	15/04/2020	80	1
15503	15/04/2020	70	2
15503	17/04/2020	70	1
15503	17/04/2020	70	2
15503	22/04/2020	70	2
15503	22/04/2020	80	1
16507	19/02/2020	80	1
16507	19/02/2020	90	2
16507	26/02/2020	90	2
16507	26/02/2020	90	1
16507	28/02/2020	80	1
16507	28/02/2020	100	2

So for student 16507, the latest test they took was on 28/02/2020 and the result should show as 100. Their second latest test was on 26/02/2020 and their result should show as 90.

What measure can I use for 'Result Latest Test' and 'Result Second Latest Test'?

[v-jialongy-msft · Accepted solution]

Hi @PBOBOP 

I have modified your dax formula and it works now.

Latest Test = CALCULATE(
    MAX('Students Tests'[Test Date]),
    ALLEXCEPT('Students Tests','Students Tests'[Student ID])
 )

Result Latest Test = 
VAR LatestTestDate = [Latest Test]
RETURN CALCULATE(MAX('Students Tests'[Results]),ALL('Students Tests'),'Students Tests'[Test Date]= LatestTestDate)

Second Latest Test = 
CALCULATE(MAX('Students Tests'[Test Date]),FILTER(ALLEXCEPT('Students Tests','Students Tests'[Student ID]),'Students Tests'[Test Date]<[Latest Test]))

Second Result Latest Test = 
CALCULATE(MAX('Students Tests'[Results]),FILTER('Students Tests','Students Tests'[Test Date] = [Second Latest Test]))

This is the result you want

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.