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

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

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 - Power BI - Enterprise DNA Forum

 

 

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

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's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    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

    • rajendraongole1's avatar
      rajendraongole1
      Icon for Super User rankSuper User
      1 year ago

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

       

      Thank you.

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

      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,