Nested IF statements in power query

Hi Power-Bi Experts,

I need help with the correct syntax for a nested if statement(s) to identify students who have completed a qualification, and students who have met the criteria to progress to the next level of their qualifications (i.e. from level 1 to level 2, and from level 2 to level 3).

My data has the following columns:

• Student number (each student has a unique ID)
• Year (identifies the calendar year in which a student is registered)
• Period (identifies the trimester in which a student is registered - NB, there are 3 trimesters in 1 calendar year. However, there were only 2 trimesters in 2020 due to covid-19 lockdown)
• Qualification (identifies the qualification a student is registered for)
• Level (identifies the qualification level a student is registered for)
• Subject (identifies the subjects each student is registered for at each qualification level)
• Final Marks (identifies the final mark a student has received for each of the subjects they are registered for)
• Completion status (identifies the subjects which have been passed by a student - using 'Pass', 'Fail', and 'Absent' if the student did not write the final exam).

Qualification structure:

• Each qualification level has 4 subjects (i.e. there are 4 subjects at level 1, 4 subjects at level 2, and 4 subjects at level 3)
• The minimum duration of each qualification level is 1 trimester (i.e. a student can be registered for a maximum of 3 qualifications in 1 calendar year, provided they meet the completion and progression criteria below)

A student must meet the following criteria to complete a qualification and be awarded a certificate:

• Pass all subjects they are registered for at each qualification level. 

A student must meet the following criteria to progress to the next level

• Pass a minimum of 3 subjects at each qualification level.
• This means a student can progress to level 2 and to level 3 of their qualification, provided they pass 3 subjects at level 1 and 3 subjects at level 2. 

Support Required

Based on the above, and considering that I am working with a dataset covering the period 2019 - 2021, my thinking is that I need to create the following custom columns (nested IF statements):

• one identifying students who have completed a qualification level and awarded a certificate (i.e. students who meet the completion criteria), and
• one identifying students who have progressed to the next level of their qualification (i.e. students who meet the progression criteria)
• If you think a Dax measure will work best here, I am open to all recommendations.

Looking forward to some guidance.

Thank you.