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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.