DAX - find the earliest date in a group and keep other columns

[JRoj]

Hello, I would need some help with DAX as I just can't get through this one task.

This is a sample data containing 5 columns with student's grades from each class, subject on given date.

Class	Subject	Name	Grade	Date
1B	Mathematics	John	5	18.01.2023
1B	Mathematics	John	5	15.01.2023
1B	Mathematics	John	4	14.01.2023
2A	Science	Andre	3	14.01.2023
2A	Science	Andre	2	12.01.2023

Using DAX formulas my goal would be: for each Student in a Class and Subject I would like to filter out only the latest grade that he was given. So the output should be:

Class	Subject	Name	Grade	Date
1B	Mathematics	John	5	18.01.2023
2A	Science	Andre	3	14.01.2023

I tried several approaches. With GROUPBY function I tried to group by Class, Subject and Name and find the latest date for each group but than I was missing the grade 😞

I also thought of joining my main table to GROUPBY output on 4 columns (Class, Subject, Name, Date) but in DAX you can't join by multiple columns 😞

This is just simplified problem. I hope you understand and can provide me with some help regarding this.

Thank you 🙂

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a calculated table.

New table =
VAR _groupbylatestdate =
    GROUPBY (
        Data,
        Data[Class],
        Data[Subject],
        Data[Name],
        "@latestdate", MAXX ( CURRENTGROUP (), Data[Date] )
    )
RETURN
    CALCULATETABLE (
        Data,
        TREATAS (
            _groupbylatestdate,
            Data[Class],
            Data[Subject],
            Data[Name],
            Data[Date]
        )
    )