Student Completion Rate

Hi Power-Bi Experts,

I am circling back on this question, and hope I am clear enough to get a solution this time.

I would like to calculate the student completion rate by:

• Period (Trimester 1, Trimester 2, Trimester 3)
• Qualification (Marketing, Finance)
• Qualification Level (Level 1, Level 2, Level 3)

Background of qualifications provided, and completion criteria:

• Each qualification level has 4 subjects. For example, we have 4 subjects for Marketing Level 1, 4 subjects for Marketing Level 2, and 4 subjects for Marketing Level 3. The same structure applies to the Finance qualifications. In essence, this means we have 6 qualifications (3 Marketing and 3 Finance qualifications).
• Duration of all qualifications is defined by trimesters (12/13 weeks), and there are 3 trimesters in 1 year.
• A student cannot be registered for 2 qualifications at a time or within 1 trimester. For example, a student cannot be registered for Marketing Level 1 and Marketing Level 2 in Trimester 1.
• Completion of qualification. A student is deemed to have completed a qualification once they pass all 4 subjects for a particular qualification. This means a student may pass, e.g., 2 subjects in trimester 1, and re-register to complete the outstanding subjects in trimester 2/3. This also means a student can complete up to 3 qualifications in 1 year.

My dataset is a bit limited, and I have the following columns on an excel spreadsheet:

• Student Unique ID
• Gender
• Race
• Period (Trimester 1, Trimester 2, Trimester 3)
• Qualification (Marketing, Finance)
• Qualification level (Level 1, Level 2, Level 3)
• Subject
• Campus
• College

The absence of complete data (pass/fail column) makes this a bit tricky to analyze. My thinking however is that one can use the student ID period, qualification, and qualification level information to create a measure/calculation to identify students that have completed their qualifications. For example, if we have records of Student A registered for Marketing Level 1 in Trimester 1, and we also have records of Student A registered for Marketing Level 2 in Trimester 2, then student A completed Marketing Level 1.

Is this possible to achieve, or there is a better approach?

Any guidance will be appreciated.

Thank you.