Help creating logic to count and return name of missing courses

[Antonio195754]

Hey all,

Not sure if this can be done but let me try and explain:  

I have a data set that has employee name in one column, training courses they have taken in another column, and whether courses are complete or not complete on what they've attempted in another column.  In total, every employee must complete 9 courses to be called 100% complete with their training.  The issue i'm having is the data only shows as mentioned earlier, what they've attempted to take, no matter if it's complete or not.  So some employees i may only see they have 5 courses next to their names (doesn't matter if it's complete or incomplete), and other employees i'll see all 9 courses (again doesn't matter if they're complete or not).  In a perfect world i would want to see all the courses, per employee and whether it's complete or not complete; unfortunately that's not the case. What i'm trying to do is, since i know the 9 course names, to find a way to make PBI call out which courses/names of courses are missing and the count of that variance.  How would i get this started?  I think i would have to make a seperate table of courses like below but being a novice in PBI, i wouldn't know how to write the DAX to say "If Employee A shows 7 courses taken in raw data, give me the count of what's missing and give me the names missing based on course name table"  And then find a way to roll that up so that it works for every employee (population of 5K~)

Please help!

Course 1

Course 2

Course 3

Course 4

Course 5

Course 6

Course 7

Course 8

Course 9

Employee	Course	Status
Joe	Course 1	Complete
Joe	Course 2	Incomplete
Joe	Course 3	Complete
Joe	Course 4	Incomplete
Joe	Course 5	Incomplete
Joe	Course 6	Complete
Joe	Course 7	Incomplete
Mary	Course 3	Incomplete
Mary	Course 4	Incomplete

[jdbuchanan71 · Accepted solution]

@Antonio195754 

We can get something like this with just a few measures:

This is the measure for Not Taken

Not Taken = 
VAR _x = VALUES ( Data[Course] )
VAR _y = CALCULATETABLE ( Courses, EXCEPT ( Courses, _x ) )
VAR _count  = COUNTROWS ( _y )
RETURN
IF ( 
    _count = 0 || NOT HASONEVALUE(Data[Employee]), BLANK(),
    "(" & _count & ") " &
    CONCATENATEX( _y, Courses[Course], ", ")
)

I have attached my sample file for you to look at.