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
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
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.