topic Re: How to get the latest exam result based on date (page filter applied for person) in DAX Commands and Tips

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

Anonymous — Fri, 08 Apr 2022 09:42:29 GMT

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)

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

tamerj1 — Fri, 08 Apr 2022 10:04:55 GMT

Hi Anonymous

You may try

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

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

Anonymous — Fri, 08 Apr 2022 12:08:47 GMT

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?

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

tamerj1 — Fri, 08 Apr 2022 12:24:49 GMT

Anonymous

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

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

Anonymous — Fri, 08 Apr 2022 12:34:16 GMT

Here it is

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

tamerj1 — Fri, 08 Apr 2022 12:49:16 GMT

Anonymous

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] ) )

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

johnt75 — Fri, 08 Apr 2022 12:51:02 GMT

You could use TOPN, like

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

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

tamerj1 — Fri, 08 Apr 2022 12:55:09 GMT

Anonymous

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

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

Whitewater100 — Fri, 08 Apr 2022 13:06:10 GMT

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

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

Anonymous — Fri, 08 Apr 2022 13:28:28 GMT

yes, exam table has its own ids

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

tamerj1 — Fri, 08 Apr 2022 13:36:17 GMT

Anonymous

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

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

Anonymous — Fri, 08 Apr 2022 14:43:31 GMT

I have uploaded the pbix file to this location.

https://easyupload.io/lktsnj

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

tamerj1 — Sun, 10 Apr 2022 06:18:55 GMT

Hi Anonymous
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] ) )

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

Anonymous — Sun, 10 Apr 2022 19:42:40 GMT

Hi Tamer;

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