Way to have PowerBI pull out student's best test score & create a table?

[afaherty]

Hello,

I need to find out if there is a way that PowerBI can pull out every student's best test scores (by test subject) and create a table of them?

For example:

Student ID	Test Subject	Test Date	Score
1	English	2/1/24	100
1	English	1/1/24	80
1	English	1/15/24	90
1	Math	3/1/24	75
1	Math	3/14/24	92
1	Math	4/1/24	100
1	Math	3/24/24	95

Here is what I'd like the result to be - a table showing this:

Student ID	Test Subject	Test Date	Score
1	English	2/1/24	100
1	Math	4/1/24	100

(There are many more students than just student #1 but you get the idea!)

Thanks in advance!!

[Selva-Salimi · Accepted solution]

Hi @afaherty 

you just need to write a column in your table as follows:

Column = if (calculate(max('Table'[Score]) , filter ('Table' , 'Table'[Test Subject]=EARLIER('Table'[Test Subject]) && 'Table'[student ID] = EARLIER('Table'[student ID]))) = 'Table'[Score] , 1 ,0)

and filter your visual (visual level filter) based on this column and set it to 1.

there are lots of other solutions but this is the easiest one that I recommend.

If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

[afaherty]

Perfect! Thank you SO much!