Filtering Users based on exam results

Hi JonBonesJones 🙂

The table/query that stores the exams is the facts table, as such, it probably has a date column and a student's name column.
From what I can understand, students can practice the exams, but you only want the last value to be considered (valid exam).

It's a bit hard to help more without looking at the whole data, but you might want to perform the following ( you can always go back and try another way if this doesn't work):

1. Duplicate that query just in case you need to have the original backed up;

2. I tried to find a way to sample your data structure:

3. Try to find a better way to deal with the data: let's try unpivoting the table
Choose the 2 columns you want to keep and click at "unpivot others"

The result will be:

Note that you can rename the columns, for example, as Tests and Grades.

4. Unload the original query( you have the backup saved)

5. Make 2 references to the transformed query

You can call to one "table max" and the other "table".

6. Go to "table max" and perform a Groupby of "Student" and "Test" by the max of date
(this will allow you to consider the last exam - it should be the valid one - by student and type of exam)

On this step, where it says "New column name" (Count), you can rename it to "Date"
Click "ok" at the end.

7. Merge your new query with the query Table max

Make sure the keys match

Click "ok"

8. Click on "Expand" (black circle) and expand only the grades

This should give you the grades that correspond to the maximum (last date) in which a student answered an exam, by exam 🙂

I'll leave the sample file attached!

Hope I was of assistance!
Cheers
Joao Marcelino

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!