Skip to main content
cancel
Showing results for 
Search instead 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

Reply
PBOBOP
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. 

PBOBOP_0-1710815889261.png

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:
PBOBOP_1-1710816994124.png

 

Here is some sample data:

Student IDTest DateResultsTest Number
145396/01/20201002
145396/01/2020801
145399/01/20201002
145399/01/2020801
1453916/01/2020801
1453916/01/20201002
145549/03/202080  2
145549/03/2020801
1455412/03/20201002
1455412/03/2020801
1455416/03/20201002
1455416/03/2020801
155036/01/2020801
155036/01/2020802
155039/01/20201001
155039/01/20201002
1550316/01/20201002
1550316/01/2020801
1550315/04/2020801
1550315/04/2020702
1550317/04/2020701
1550317/04/2020702
1550322/04/2020702
1550322/04/2020801
1650719/02/2020801
1650719/02/2020902
1650726/02/2020902
1650726/02/2020901
1650728/02/2020801
1650728/02/20201002

 

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
v-jialongy-msft
Community Support
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

vjialongymsft_0-1710913789264.png

 

 

 

Best Regards,

Jayleny

 

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

 

View solution in original post

1 REPLY 1
v-jialongy-msft
Community Support
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

vjialongymsft_0-1710913789264.png

 

 

 

Best Regards,

Jayleny

 

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

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors