Count number of users with ONLY a specific status

[RE]

I have a table of users and the online course work that they have been assigned. Each user has multiple records because they are assigned several different courses and they can have a different status for each course. The three statuses are completed, in progress, and subscribed. I am trying to calculate the number of users that have never logged into a course (so users with a status of subscribed ONLY). Any measure/column I write counts users that have a status of subscribed, complete, and/or in progress. 

The latest calculated column I wrote looks like this: 

Never Logges In =
IF(TrainingTbl[Status] <> "Completed", TRUE(), FALSE()) &&
IF(TrainingTbl[Status] <> "In Progress", TRUE(), FALSE()) &&
IF(TrainingTbl[Status] = "Subscribed", TRUE(), FALSE())

[bdymit · Accepted solution]

Users Only Subscribed =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            TrainingTbl,
            TrainingTbl[Name],
            "Num Subscribed", CALCULATE ( COUNTA ( TrainingTbl[Status] ), TrainingTbl[Status] = "Subscribed" ),
            "Num Courses", CALCULATE ( COUNTA ( TrainingTbl[Status] ) )
        ),
        [Num Subscribed] = [Num Courses]
    )
)

See if this works. I am not sure how large your data set is, so it might be a little slow. 

It uses SUMMARIZE to create a virtual table that looks like this:

Name   Num Subscribed   Num Courses

Joe        3                             4

Nick      5                             5

Kate      1                             1

Sue       0                             4

It counts the number of courses assigned to the person, and then the number of courses with the Status "Subscribed"

Then it filters that table to people where the number of courses and number subscribed are the same.

Name   Num Subscribed   Num Courses

Nick      5                             5

Kate      1                             1

Then it counts the rows.

2

Let me know if that works in your data model.

Cheers!

Ben

[mattbrice]

if you just need a number:

measure =
CALCULATE ( COUNTROWS ( TrainingTbl ), TrainingTbl[Status] = "Subscribed" )
 

So if a student is "Subscribed", it will count the student.  If the same student is "Subscribed" to 2 classes, this will count the student twice (and so on)

If you want a count of the unique students that are "Subscribed" to at least one course, you can do this:

measure =
CALCULATE (
    DISTINCTCOUNT ( TrainingTbl[StudentID] ), TrainingTbl[Status] = "Subscribed"
)