Nested IF statements in power query

[MagubaneSSP]

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.

[Anonymous]

HI @MagubaneSSP,

Account to your description, I think Dax expression should be more suitable for your requirement. Can you please share a pbix or some dummy data that keep raw data structure with expected results? 

How to Get Your Question Answered Quickly  

Notice:

The power query formula is good at the shape and transforms table structure, then you can simply use these records to design reports. (for this requirement, you need to extract current table records to use as parameters and looping/check matched records with conditions, they needed to use complex nested M query functions with poor process performance)

Regards,

Xiaoxin Sheng

[TomMartens]

Hey @MagubaneSSP ,

thank you very much for the detailed description. Please take the time and create a pbix file that contains sample data, but still reflects your data model (tables, relationships, calculated column, and measures). Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, share the xlsx as well.

Desribe the expected result based on the sample data you provide.

Regards,

Tom