How to get the latest exam result based on date (page filter applied for person)

[tolgaa]

I have 2 tables as shown below ( tables have 1-* relation based on names)

Students:

Id	Name
1	Joseph Hernandez
2	Humberto Jones
3	Todd Thornton
4	Chang Bernard
5	Thanh Christensen
6	Myrna Joyce
7	Jan Walters
8	Velma Cox
9	Fran Horne
10	Ila Becker

Exams

id	name	exam	date
1	Joseph Hernandez	23	Jan-22
2	Humberto Jones	94	Jan-22
3	Todd Thornton	92	Jan-22
4	Chang Bernard	91	Jan-22
5	Thanh Christensen	14	Jan-22
6	Jan Walters	68	Jan-22
7	Velma Cox	69	Jan-22
8	Fran Horne	8	Jan-22
9	Ila Becker	56	Jan-22
10	Joseph Hernandez	47	Feb-22
11	Humberto Jones	82	Feb-22
12	Todd Thornton	33	Feb-22
13	Chang Bernard	81	Feb-22
14	Thanh Christensen	99	Feb-22
15	Myrna Joyce	41	Feb-22
16	Jan Walters	41	Feb-22
17	Velma Cox	92	Feb-22
18	Fran Horne	27	Feb-22
19	Todd Thornton	15	Mar-22
20	Chang Bernard	72	Mar-22
21	Thanh Christensen	64	Mar-22
22	Myrna Joyce	69	Mar-22
23	Velma Cox	56	Mar-22
24	Fran Horne	49	Mar-22
25	Ila Becker	57	Mar-22

On the PowerBI desktop I create a report and there is a page filter based on "students name" (one student selected).

In this page I would like to put a "card" element which shows the latest exam result of the student. 

I will appreciate if you can guide me how to create new column / measure to show the latest exam result (eg: Joseph got 47 on Feb22 and not attended any exam on Mar-22, so result shall represent 47)

[tolgaa]

Hi Tamer;

Thank you very much for the answer. It works like a magic.

[Whitewater100]

Hi:

Please see link for either calculated column or measure. Measures are genrally better,except if you need a sort field.

If names are joined on a numerical id vs. a string, that is a good move too. I added a date table so you can use time intel functions..

I hope this is a good solution for you.https://drive.google.com/file/d/1n8HVWo-ScrlD4HpQlweUOuLHhH9qkPue/view?usp=sharing 

[johnt75]

You could use TOPN, like

Latest result = SELECTCOLUMNS( TOPN( 1, Exams, Exams[Date]), "@val", Exams[Result])

[tamerj1]

Hi @tolgaa 

You may try

Last Exam Result =
IF (
    HASONEVALUE ( Results[name] ),
    CALCULATE (
        VALUES ( Results[exam] ),
        ALLEXCEPT ( Results, Results[name] ),
        Results[date] = MAX ( Results[date] )
    )
)

[tolgaa]

Hi;

I have tried your suggestion unfortunately it didn't work for me.

Last Exam Result =

IF (

HASONEVALUE ( exams[name] ),

CALCULATE (

VALUES ( exams[exam] ),

ALLEXCEPT ( exams, exams[name] ),

exams[date] = MAX ( exams[date] )

)

)

Do you have any idea what might be the problem?

[tamerj1]

@tolgaa 

For "name" in the filter pane, would you please select "is not blank" and click apply. What results do you get?

[tolgaa]

Here it is

[tamerj1]

@tolgaa 

Ok. Now please try this. If you recieve an error then the problem would be having multiple exam results for the same petson in the same day. In this case you need to decide which result would you like display (the max or min for example)

Last Exam Result =
VAR NameTable =
    CALCULATETABLE ( exam, ALLEXCEPT ( exams, exams[name] ) )
VAR lastExamDate =
    MAXX ( NameTable, exams[date] )
RETURN
    IF (
        HASONEVALUE ( exams[name] ),
        SELECTCOLUMNS (
            FILTER ( NameTable, exams[date] = lastExamDate ),
            "Exam", exams[exam]
        )
    )

[tamerj1]

@tolgaa 

Also one more thing. I can see in this screenshot that for each name there is multiple Id numbers. Is this the exam ID? 

 

[tolgaa]

yes, exam table has its own ids

[tamerj1]

@tolgaa 

So did you try the last code? Did you receive an error?

[tolgaa]

I have uploaded the pbix file to this location.

https://easyupload.io/lktsnj

[tamerj1 · Accepted solution]

Hi @tolgaa 
Applogies for the late reply.

Here is a the sample file with the solution https://www.dropbox.com/t/bSsLhvMUUkaCIQm8
Actually you were adding the code as a column where my code was for a measure. The HASONEVALUE is blanking out the result when you add it as a column. Here are both solutions:

> New Column > 

Last Exam Result = 
VAR NameTable =
    CALCULATETABLE ( exams, ALLEXCEPT ( exams, exams[name] ) )
VAR lastExamDate =
    MAXX ( NameTable, exams[date] )
RETURN
    SELECTCOLUMNS (
        FILTER ( NameTable, exams[date] = lastExamDate ),
        "Exam", exams[exam]
    )

> New Measure >

Last Exam Result Measure = 
VAR NameTable =
    CALCULATETABLE ( exams, ALLEXCEPT ( exams, exams[name] ) )
VAR lastExamDate =
    MAXX ( NameTable, exams[date] )
RETURN
    IF (
        HASONEVALUE ( exams[name] ),
        SELECTCOLUMNS (
            FILTER ( NameTable, exams[date] = lastExamDate ),
            "Exam", exams[exam]
        )
    )