cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vozniak
Helper I
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
Greg_Deckler
Super User
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!

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
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!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.