cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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'?

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.