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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gancw1
Helper III
Helper III

Display most recent record or highest value for each category

I have a table of student marks with the date the tests are taken.

StudentDateMarks
A10/9/202250
A11/9/202290
A15/9/202280
B11/9/202290
B12/9/202270
C10/9/202285
D14/9/202250
D16/9/202260


How do I create tables showing the first attempt marks, the last attempt marks and the highest marks for each student  ?

 

First attempt 
StudentDateMarks
A10/9/202250
B11/9/202290
C10/9/202285
D14/9/202250

 

Last attempt 
StudentDateMarks
A15/9/202280
B12/9/202270
C10/9/202285
D16/9/202260

 

Highest marks 
StudentDateMarks
A11/9/202290
B11/9/202290
C10/9/202285
D16/9/202260

 

 

1 ACCEPTED SOLUTION
3 REPLIES 3

@amitchandak 

 

In your code

Last Status = var _max = maxx(filter(ALLSELECTED(Data), Data[ID] = Max(Data[ID])), Data[Date]) 
return
CALCULATE(max(Data[Status]), filter((Data) , Data[Date] =_max))

 

What does this expression do ?

Data[ID] = Max(Data[ID])

 

@amitchandak,

Thanks.

I managed to display the date and marks for the first and last attemps.

First Date = MINX( 
FILTER(ALLSELECTED(Table_Marks), Table_Marks[Student]=MIN(Table_Marks[Student])),
Table_Marks[Date] )

First Mark =
    CALCULATE(
        max(Table_Marks[Marks]), FILTER(Table_Marks, Table_Marks[Date]= [First Date]))

However I am unable to get the date for the highest marks.

I have these 2 measures :

Highest Mark = max(Table_Marks[Marks])
Highest Marks Date = CALCULATE(max(Table_Marks[Date]), FILTER(Table_Marks, Table_Marks[Marks]= [Highest Mark]))

 

This will give an incorrect date when a lower mark is attained later

 

In addition, how do I calculate the Average of all the last attempt marks ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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