Identify students that failed previous course

[jsbourni]

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 count the number of times a course had to be taken again follwing a previous attempt. With the appropriate slicers I should be able to see which courses were failed.

I'm not sure how to conceptualize that measure. Comparing a table with itself in DAX seems rather counterintuitive (as opposed to, say, SQL).I was going for variables to create tables to compare but can't find the right logic.

Thanks,

[Anonymous]

Hi @jsbourni ,

You can try formula to crreate calculated column and measure:

FailedCourse = IF(StudentData[Grade] = "E", 1, 0)

RetakeCount =
VAR course_ =
    MAX ( StudentData[Course] )
VAR id_ =
    MAX ( StudentData[ID] )
VAR semester_ =
    MAX ( StudentData[Semester] )
VAR minDate =
    MINX (
        FILTER (
            StudentData,
            StudentData[FailedCourse] = 1
                && StudentData[Course] = SELECTEDVALUE ( StudentData[Course] )
                && StudentData[ID] = id_
        ),
        StudentData[Semester]
    )
RETURN
    CALCULATE (
        COUNTROWS ( StudentData ),
        FILTER (
            StudentData,
            StudentData[FailedCourse] = 1
                && StudentData[Course] = SELECTEDVALUE ( StudentData[Course] )
                && StudentData[Semester] > minDate
        )
    )

FailureCount = 
CALCULATE(
    COUNTROWS(StudentData),
    FILTER(
        StudentData,
        StudentData[FailedCourse] = 1
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PavanLalwani]

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]

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.

[divyed · Accepted solution]

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

[jsbourni]

Hi @divyed,

It seems to work perfectly. Thanks.

[rajendraongole1]

Hi @jsbourni - create a calculated column to identify failed courses (grades with "E").

Failed Course = IF([Grade] = "E", 1, 0)

create another measure that checks for retakes

Retaken Course =
CALCULATE(
COUNTROWS('Table'),
FILTER(
'Table',
'Table'[Student_ID] = EARLIER('Table'[Student_ID]) &&
'Table'[Course] = EARLIER('Table'[Course]) &&
'Table'[Semester] > EARLIER('Table'[Semester])
)
)

replace with your model table name and columns

Ref links:

Count the number of successes/failure and combine ... - Microsoft Fabric Community

How To Calculate Number Of Completed Courses Per Student - Dynamic Grouping In Power BI using DAX - ...

[jsbourni]

Hi @rajendraongole1,

Thank you for this quick answer. I tried it does not work as I get an error message on the usage of the Earlier function which refer to a previous row that does not exist.

Also, I'm not sure to understand the logic of the calculated column. I presume it is to filter on failed courses, but I'm not sure how people that passed the second time they took the course will show. Maybe I do not understand well enough the context transition.

Just to be sure, my goal is to calculate how many students/courses were retakes.

Hope this makes sense.

Thanks

[rajendraongole1]

Hi @jsbourni - FIne, you can still use the above created calculated column (if condition)

Failed Course = IF([Grade] = "E", 1, 0)

Now, create a measure to count retakes by comparing semesters.

RetakeCount =
VAR CurrentStudent = StudentData[StudentID]
VAR CurrentCourse = StudentData[Course]
VAR CurrentSemester = StudentData[Semester]

RETURN
CALCULATE(
COUNTROWS(StudentData),
FILTER(
StudentData,
StudentData[StudentID] = CurrentStudent &&
StudentData[Course] = CurrentCourse &&
StudentData[Semester] > CurrentSemester &&
StudentData[Grade] <> "E"
)
)

you want to track students who failed and then passed (or retook) the course

CountRetakes =
CALCULATE(
COUNTROWS(StudentData),
FILTER(
StudentData,
StudentData[StudentID] = EARLIER(StudentData[StudentID]) &&
StudentData[Course] = EARLIER(StudentData[Course]) &&
StudentData[Semester] > EARLIER(StudentData[Semester]) &&
StudentData[Grade] <> "E"
)
)

Hope this time it works

[jsbourni]

Hi @rajendraongole1,

Thank you for your time. The EARLIER function always gives me trouble. I will deepen my understanding on this as it seems to be really helpful. Best,

[rajendraongole1]

Hi @jsbourni - if the above logic works, please mark it as a solution. 

Thank you.