Forum Discussion
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 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,
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
10 Replies
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
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
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
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
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!
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.
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 KongIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
