Rank last subject course passed

supergallagher25 · August 20, 2021, 6:21pm

Hello,

I am trying to find the last course that I student passed. I have the following table,

I’m trying to find the last course that a student would have passed. Normally I use ranking based on date. However, this seems a little tricky. Any help or suggestions would be greatly appreciated.

BrianJ · August 20, 2021, 6:39pm

@supergallagher25 ,

4 questions:

what’s considered a passing score?
is “last” by subject - i.e., “last” here (without respect to passing or not) would be Eng 104, Mth 105 and Sci 101?
do you care whether the solution is PQ or DAX?/ Personally, I would definitely do all of this in PQ
Is is correct that some Subjects will be entered as Eng 101 while others will be Eng102

Thanks

Brian

supergallagher25 · August 20, 2021, 7:14pm

Passing score would be greater than or equal to 50%. If they do not hit that benchmark, then I’m trying to show the last course they passed. And yes I meant to type in subject.

I normally do anything related to ranking using Dax but am definitely open to learning options in power query.

Subjects are basically the different rankings of the same core course.

BrianJ · August 20, 2021, 7:18pm

@supergallagher25 ,

OK, got it - thanks! Because I’m super lazy and a terrible typist, can you please attach a copy of the excel file with the sample data.

Brian

supergallagher25 · August 20, 2021, 7:32pm

ID	Subject	Score (out of 10)	Passed
1	Eng 101	5	50.00%
10	Eng102	6	60.00%
1	Eng 103	1	10.00%
10	Eng 104	3	30.00%
1	Mth 101	5	50.00%
10	Mth 102	7	70.00%
1	Mth 103	9	90.00%
10	Mth 104	10	100.00%
1	Mth 105	5	50.00%
10	Sci 101	7	70.00%

Thank you for your time and assistance.

BrianJ · August 20, 2021, 7:36pm

Thanks – sorry one more question. I’m assuming ID 1 & 10 are separate students and you want Max passed results by subject for each separately?

supergallagher25 · August 20, 2021, 7:37pm

Yes that’s what I’m trying to look into.

BrianJ · August 20, 2021, 8:53pm

@supergallagher25 ,

Pretty much for any problem that involves grouping, I tend to think Power Query is much easier than DAX.

Here’s the approach I took based on the incredibly powerful combination of All Rows and Groupby (see video I did on this topic below for more details).

The key steps were just to:

create a conditional column for passed courses (those that scored 50% or more)

do the all rows/group by to compute the max course passed per subject and student

Here it is all put together:

I hope this is helpful. I think once you get the hang of the group by, you’ll find a million and one uses for it.

Full solution file attached below.

– Brian

e DNA Forum – Max Passed Courses Solution.pbix (23.3 KB)

EnterpriseDNA · August 23, 2021, 2:05am

Hi @supergallagher25, good to see you are making progress with your query.

Did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

EnterpriseDNA · August 24, 2021, 12:28am

Hi @supergallagher25, we’ve noticed that no response has been received from you since August 21.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

EnterpriseDNA · August 25, 2021, 1:37am

Hi @supergallagher25, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.