Forum Discussion

jsbourni's avatar
jsbourni
Icon for Helper II rankHelper II
1 year ago
Solved

Identify students that failed previous course

Hi,   I'm using basic student data like ID, semester, course, and grade. Some students fail a course (letter "E" in grade) and have to retake the course on a further semester. I would like to coun...
Need Help
Show and Tell
  • divyed's avatar
    divyed
    1 year ago

    Hello jsbourni ,

     

    It would be better if you add sample data (remove sensitive if any) and output to check further. Meanwhile you can try below steps :

     

    1. Create a Calculated Column to Identify Retakes:

        //calculated column that flags when a course is retaken by a student after failing.

        IsRetake =
    VAR PrevAttempt =
    CALCULATE(
    MAX('StudentData'[Semester]),
    ALLEXCEPT('StudentData', 'StudentData'[ID], 'StudentData'[Course]),
    'StudentData'[Semester] < EARLIER('StudentData'[Semester]),
    'StudentData'[Grade] = "E"
    )
    RETURN IF(ISBLANK(PrevAttempt), 0, 1)

     

    2. Create a Measure to Count Retakes: 

        RetakeCount =
    CALCULATE(
    COUNTROWS('StudentData'),
    'StudentData'[IsRetake] = 1
    )

    //This measure sums the number of retakes across the dataset where the flag is set to 1

     

    I hope this helps .

     

    Did I solve your query , if yes kindly mark this as solution :).

     

    Cheers

PavanLalwani's avatar
PavanLalwani
Icon for Resolver II rankResolver II
1 year ago

To identify students who failed a course and had to retake it in Power BI, you can use DAX to compare current and previous attempts by each student for the same course. Here's a general approach:

1. **Create a Calculated Column**: Add a column that identifies if the student failed (grade = "E") in their previous attempt.

2. **DAX Formula**:
```DAX
PreviousGrade =
CALCULATE(MAX(StudentData[Grade]),
FILTER(StudentData,
StudentData[ID] = EARLIER(StudentData[ID]) &&
StudentData[Course] = EARLIER(StudentData[Course]) &&
StudentData[Semester] < EARLIER(StudentData[Semester])
)
)
```

3. **Measure for Retakes**: Use `IF` to count when a student previously failed and had to retake.

Let me know if you need more details!

  • jsbourni's avatar
    jsbourni
    Icon for Helper II rankHelper II
    1 year ago

    Hello PavanLalwani,

    Thanks for the answer. The problem I have is that the EARLIER function always returns errors as I don't have unique values in my variables. Cheers.