Highest Value From a List

[TiMark2000]

Hi,

This is probably pretty straight forward fix but I was struggling to find a solution to this specific issue.

I have two tables, User Table and Quiz Table. The quiz table stores all the users attempts at the quiz and I want to record there best score for each user from all there attempts and record it in the User Table. Currently I'm trying to use this to try and get a list of all the current users attempts:

MAX("Quiz Table", "Mark", ([User ID] = [Quiz Table[USER ID]))

The error I'm getting is "Cannot compare Text with List in ([User ID] = Social Media Quiz[USER ID])". Ive tried a few different things like [_thisrow] but I don't know if I'm using it correctly.

Thanks for the help.

[WillowMobileSys · Accepted solution]

The portion of your expression, "Quiz Table[User ID]", simply return a list of all User ID's that appear in Quiz Table - duplicates and all.  Comparing that list to [User ID] is what is causing the trouble.  You want to filter by that User ID.

The MAX() function is the correct function to use but you need to return the list of Quiz Scores for it to find the max one.

So putting this all together, the expression you want is:

MAX(SELECT(Quiz Scores[Mark], [User ID] = [_THISROW].[User ID]))

This assumes that the user column is named "[User ID]" in both the Quiz Scores table as well as this current table you are trying to assign the Max Score into.