cancel
Showing results for
Did you mean:
Helper I

Comparing values in the same column

I have a table of test scores where each student as a potential 4 scores with different IDs. I need to be able to filter the IDs to find which is the lowest or highest for each student.

Example:

Student 1

Test1  50

Test2 40

Test3 30

Test4 20

Student 1 lowest score Test4

Once I know which test had the lowest score I can compare their score against the passing criteria and determine if they passed or not.

Any help would be much appreciated!!

1 ACCEPTED SOLUTION
Super User

Use MINX to find the lowest score for a student and then you can use FILTER to filter down to that value for the student.

For example, if you have

Student,Test,Score

Student1, Test1,20

Student1, Test2,40

Student2, Test1,50

Student2, Test2,20

Then you could do something like:

```VAR __student = MAX([Student])
VAR __table = FILTER(Table,[Student]=__student)
VAR __lowest = MINX(__table,[Score])
RETURN
MAXX(FILTER(__table,[Score]=__lowest),[Test])
```

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Super User

Use MINX to find the lowest score for a student and then you can use FILTER to filter down to that value for the student.

For example, if you have

Student,Test,Score

Student1, Test1,20

Student1, Test2,40

Student2, Test1,50

Student2, Test2,20

Then you could do something like:

```VAR __student = MAX([Student])
VAR __table = FILTER(Table,[Student]=__student)
VAR __lowest = MINX(__table,[Score])
RETURN
MAXX(FILTER(__table,[Score]=__lowest),[Test])
```

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Announcements

Launching new user group features

Learn how to create your own user groups today!