Help creating a column that lists the exam score associated with the lowest value in another column.

[lyengulalp]

Hello.

I have a table with students' exams including score, exam number, and whether the exam was proctored or not. 

Name	Exam score	Exam number	Proctored
Kathy	90	1	No
Kathy	86	2	Yes
Kathy	70	3	Yes
Ben	79	1	Yes
Ben	94	2	No
Steve	20	1	No
Steve	35	2	Yes
Steve	60	3	No
Steve	81	4	Yes

I would like to produce tables that list the students together with the score on the first proctored exam:

Name	First proctored exam score
Kathy	86
Ben	79
Steve	35

I've tried a bunch of things and can't get them to work. 

Any help would be greatly appretiated!

[lyengulalp]

@Fowmy Thank you.

[Fowmy · Accepted solution]

@lyengulalp 

I hope your exam numbers are in sequence order otherwise you will have to add an index column in Powre Query and use it instead. Try this meaure:

First proctored exam score = 
VAR __ExNo =     
    MINX(
        FILTER(
            Table1, 
            Table1[Proctored] = "Yes"
        ),
        Table1[Exam number]
    )
VAR __Result =
    CALCULATE(
        MAX(Table1[Exam score]),
        Table1[Exam number] = __ExNo
    )
RETURN
    IF( 
        ISINSCOPE( Table1[Name] ),
        __Result
    )