Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
tolgaa
Frequent Visitor

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

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

Students:

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

 

Exams

idnameexamdate
1Joseph Hernandez23Jan-22
2Humberto Jones94Jan-22
3Todd Thornton92Jan-22
4Chang Bernard91Jan-22
5Thanh Christensen14Jan-22
6Jan Walters68Jan-22
7Velma Cox69Jan-22
8Fran Horne8Jan-22
9Ila Becker56Jan-22
10Joseph Hernandez47Feb-22
11Humberto Jones82Feb-22
12Todd Thornton33Feb-22
13Chang Bernard81Feb-22
14Thanh Christensen99Feb-22
15Myrna Joyce41Feb-22
16Jan Walters41Feb-22
17Velma Cox92Feb-22
18Fran Horne27Feb-22
19Todd Thornton15Mar-22
20Chang Bernard72Mar-22
21Thanh Christensen64Mar-22
22Myrna Joyce69Mar-22
23Velma Cox56Mar-22
24Fran Horne49Mar-22
25Ila Becker57

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)

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

 


 

View solution in original post

13 REPLIES 13
tolgaa
Frequent Visitor

Hi Tamer;

 

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

Whitewater100
Solution Sage
Solution Sage

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 

 

Whitewater100_0-1649423127625.png

 

johnt75
Super User
Super User

You could use TOPN, like

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

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

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] )
)
)
 
tolgaa_0-1649419674385.pngtolgaa_1-1649419696479.png

Do you have any idea what might be the problem?

 

@tolgaa 

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

tolgaa_0-1649421239191.png

Here it is

 

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

 

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

E15E5D37-0863-425B-A7CA-CECEDA7B9E78.png

yes, exam table has its own ids

@tolgaa 

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

tolgaa_0-1649428870656.png

I have uploaded the pbix file to this location.

https://easyupload.io/lktsnj

 

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

 


 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors