Show latest 3 exam scores per student in a table/matrix

[samirfarid]

Dear community, I have been searching for an answer to this for a couple of hours but I can't really find a solution/starting point.

I'm trying to show the latest 3 known scores of every student in a table/matrix. The data is as follows: 

Exams

StudentID	ExamScore	ExamDate
1	100	1/13/2019
2	90	1/13/2019
1	80	2/16/2019
1	95	3/10/2019
1	70	4/12/2019
3	60	3/10/2019

Students

StudentID	StudentName
1	Jack
2	John
3	Mary
4	Stevano
5	Jamal

The goal of the visual is to inform a teacher/coach about the latest 3 scores of each student, so the teacher for example knows whether a student needs extra attention. I had the following table / matrix visual in mind:

Student	Score 1	Score 2	Score 3
Jack	80	95	70
John			90
Mary			60

I really appreciate all the help.

[AnthonyTilley · Accepted solution]

So i would do this by first adding a grouped rank colunm to the exam table that will give a number in decending order to each exam split by student ordered by date desc

Rank = RANKX(FILTER(Exams,Exams[StudentID]=EARLIER(Exams[StudentID])),Exams[ExamDate],Exams[ExamDate],DESC,Dense)

i would then add three colunms to the student table that calulcate the score based on the id and the rank number from above

Score 1 =

--set the rank to your required score

Var r = 1

-- get the student id from the row

var sid = Students[StudentID]

--calculate the sum of the score where student id equals Var sid and rank equals Var r

var lu = CALCULATE(sum(Exams[ExamScore]),Exams[StudentID]=sid,Exams[Rank]=r)

--Return the calculated score

return lu

you can repeat this formula three times to givce you the 3 required scores by changing the value for R

i have made a PBIX file that should do what you are asking for 

PBIX FILE

[samirfarid]

@AnthonyTilley Sorry, back again 😞 The customer requirements changed a bit and now he wants to just show the latest 3 exam scores in a year so the result should be as follows:

Student	Exam 1 score	Exam 2 score	Exam 3 score
Jack	70	95	80
John	90
Mary	60

As the filter should take year into account I created a column for Year in the exam table and changed the RankX to be:

Rank = 
RANKX(
    FILTER(
        Exams, 
        Exams[StudentId]=EARLIER(Exams[StudentId]) && 
        Exams[ExamDateYear]=EARLIER(Exams[ExamDateYear])
    ),
    Exams[ExamDate],Exams[ExamDate],ASC,Dense)

But I can't seem to figure out how to proceed from here to get the required results.

[AnthonyTilley]

you will need to make the following changes

in the exam table you need a year colunm and change the rank formula 

Rank =

RANKX(

FILTER(Exams,

Exams[StudentID]=EARLIER(Exams[StudentID])

&&

Exams[year]=EARLIER(Exams[year])

),Exams[ExamDate],Exams[ExamDate],DESC,Dense)

second in your students table you will need to ad a year and change the Scores measures to include a filter on the year

Score 1 =

Var y = VALUE(Students[Year])

Var r = 1

var sid = Students[StudentID]

var lu = CALCULATE(sum(Exams[ExamScore]),Exams[StudentID]=sid,Exams[Rank]=r,Exams[Year]=y)

return lu

make sure you change all three measures 

i have amended the PBIX file i shared to meet your new spec

PBIX FILE

[samirfarid]

@AnthonyTilleyThanks you for your reply, but how can I change the formula to show this instead:

Student	Score 1	Score 2	Score 3
Jack	80	95	70
John	90
Mary	60

So the logic should select the last 3 exams based on the date and then show the first exam of the last 3 in the first column the second last in the second column and the last in the last column.

[AnthonyTilley]

Updeted the file again 

there may be an easier way to do this that im not seeing but for me i would introduce a second rank colunm in the exam table that will filp the rank after it is calculated.

in this file there is a rank and rank2 colunm

and the colunms in students now pull rank2

PBIX File

[samirfarid]

Works like a charm! Thank you so much for taking the effort and time to help out Anthony!