Calculate or SumX or what to add up courses for a student

[Klabonty]

The goal is to see if the student has dropped all their classes.  So in this case we would add up all the Add Courses (4 in this case) and subtract Drop Course (1 in this case) for a total of 3.  If the result is greater than zero the student still has classes and is considered active.  I need to do this for the entire table of course by student.

Need a DAX formula please (driving me crazy)

Thank you,

Kevin

[daxer-almighty · Accepted solution]

It's doable in DAX but it should be calculated in PQ. I assume, of course, you're talking about a calculcated column, not a measure. But if you're talking about a measure, you still should create a column that will derive the status for each particular student id from the data in the table.

Here it is in DAX (but you really should do it in PQ):

[Student Active] = // calculated column
var CurrentStudentID = T[ID]
var NetEffect =
    sumx(
        FILTER(
            T,
            T[ID] = CurrentStudentID
        ),
        T[Add Course] - T[Drop Course]
     )
var Result = NetEffect > 0
return
    Result

[Klabonty]

Your DAX code worked!  I now have added your column but now need to add up the students that have dropped all classes (abandoned).  In this sample data set  below if the student has abandoned I use your formula and get 1's in the student abandon column.  Now I want to count the students that abandoned. In this case one student abandoned and I have 4 abandon records for that student but I have a total of 3 students.  I keep getting sum's and calculations with the answer of 4 instead of 1 student.  Any help would be appreciated!

[Klabonty]

Thank you it worked!