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.

[Icey]

Hi @Antonio195754 ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,

Icey

[Antonio195754]

I plan to try out the suggestions today!  I may need to look at how to separate each count/name of course by row by person but i'll see how it looks like at first and go from there

[dedelman_clng]

Hi @Antonio195754 - you were on the right track with having a separate table for the courses.  What you need to do is create a table that has all employees and all courses, then "join" to the status table to find out which ones are missing.

Create a New Table and use the following code:

Not Started =
EXCEPT (     //This takes all rows from the 1st table that don't match a row in the 2nd table
    CROSSJOIN ( Courses, DISTINCT ( CourseStatus[EmpID] ) ),   //This is all emp with all courses
    SUMMARIZE ( CourseStatus, Courses[CourseID], CourseStatus[EmpID] )  //This is employees+course pairs that exist in the status table
)

You can then use that code to display the courses missing, or do more calculations against it.

Hope this helps

David

[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.

[Antonio195754]

Sorry for the delay in reply but my PBI crashed and i was out for a few weeks.  Just got back up and running.  

So i plugged in my data points to your logic and it took the formula however it did something interesting.  For both the complete and incomplete it returned all 9 courses.  What's more interesting though is i have these card visuals at the top of my dashboard that shows what % of complete per course as you slice and dice in the db.  Well, when i click on a row within the complete column for a given employee, it does make my cards count the courses they have taken by showing 100% in the cards with the respective course name and then blanks in the cards for the incompletes.  For the incompletes it does the same thing, shows all 9 courses.  And for the not taken it returns a blank field.  I have my columns i use set to text the way you do.  I don't have my tables joined (bc i tried that thinking that would work and it didn't).  So i think my formula is just a little off but almost there.  

Last, rather than the data being in a row, is there a way to write the DAX that it shows the name of the course in rows alongside the name for the 3 formulas?  That's ultimately what i need b/c i would then use that to create my count and % of complete/not complete for the entire population of employees.

Below are the formulas i used

Not Complete =

VAR _x = CALCULATETABLE( VALUES(Courses[Courses]), Compliance[COURSE_STATUS] = "Not Complete" )

VAR _count = COUNTROWS ( _x )

RETURN

IF (

_count = 0 || NOT HASONEVALUE(Compliance[PERSON_NUMBER]), BLANK(),

"(" & _count & ") " &

CONCATENATEX( _x, Courses[Courses], ", ")

)

Completed =

VAR _x = CALCULATETABLE( VALUES(Courses[Courses]), Compliance[COURSE_STATUS] = "Complete" )

VAR _count = COUNTROWS ( _x )

RETURN

IF (

_count = 0 || NOT HASONEVALUE(Compliance[PERSON_NUMBER]), BLANK(),

"(" & _count & ") " &

CONCATENATEX( _x, Courses[Courses], ", ")

)

Courses Not Taken =

VAR _x = VALUES ( Courses[Courses] )

VAR _y = CALCULATETABLE ( Courses, EXCEPT ( Courses, _x ) )

VAR _count = COUNTROWS ( _y )

RETURN

IF (

_count = 0 || NOT HASONEVALUE(Compliance[PERSON_NUMBER]), BLANK(),

"(" & _count & ") " &

CONCATENATEX( _y, Courses[Courses], ", ")

)

and it's returning something like this (scrubbed the course names some)

Name	Complete	Courses Not Taken	Not Complete
PR1234	(9) Ética 2392054, orrupción 1044, Política 3031, Norma 92060, Polít31122, Polít 2053, presarial 2055, PC 1205, Eras Partes 2056	Blank	(9) Ética 2392054, orrupción 1044, Política 3031, Norma 92060, Polít31122, Polít 2053, presarial 2055, PC 1205, Eras Partes 2056

Looking to have it like:

Name	Complete	Courses Not Taken	Not Complete
PR1234	Course 1
PR1234		Course 2	Course 2
PR1234	Course 3

[jdbuchanan71]

@Antonio195754 

Part of your measures are looking at the wrong tables, that is why you are getting all 9.

Completed = 
VAR _x = CALCULATETABLE( VALUES ( Compliance[Course] ), Compliance[Status] = "Complete" )
VAR _count  = COUNTROWS ( _x )
RETURN
IF (
    _count = 0 || NOT HASONEVALUE ( Compliance[PERSON_NUMBER] ), BLANK(),
    "(" & _count & ") " &
    CONCATENATEX( _x, Compliance[Course], ", " )
)

Incomplete = 
VAR _x = CALCULATETABLE( VALUES ( Compliance[Course] ), Compliance[Status] = "Incomplete" )
VAR _count  = COUNTROWS ( _x )
RETURN
IF ( 
    _count = 0 || NOT HASONEVALUE ( Compliance[PERSON_NUMBER] ), BLANK(),
    "(" & _count & ") " &
    CONCATENATEX( _x, Compliance[Course], ", " )
)

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

Not sure I understand your desired output.  How can course 2 be both "not taken" and "not complete" for PR1234?  Is it something like this you are looking for?

You have to pull the employee names from the compliance table and the course (column 2) from the courses table.  This will create the cross join but will throw an error until you put a measure on the view as well.  There are new measures in my sample for you to look at. ([Completed 2], [Incomplete 2], [Not Taken 2])

You can shrink the column width on the course name so it doesn't show in the table if you want to hide it.

[Antonio195754]

@jdbuchanan71 

Hey there i finally got it to work and apologies for the slow update on my end.  Working multiple dashboards at one time and can only hit this one when i have bandwidth to.  I should have provided my end goal of what i'm trying to accomplish as this is step one.  Ultimately for me, if the course is not Complete OR not Taken, it should count as not complete.  So my apologies on seperating the two but i would like for if a user is has courses not complete and/or not taken, to show up together.  But my end goal really is to get a count of what hasn't been taken by user(s) so that i can calculate the % complete and % not complete per user.  Could you help with this @jdbuchanan71 ?  As you're probably aware i can't change my measure to a count/distinct count and i've tried creating a column to help me combine the two measures of not complete/not taken but that has failed me too.  What i'd like to do is just create a formula that gives me the count of not complete (again whether the course(s) have not been taken or shows incomplete) for a user(s) so that i can then use that count to attain a % of not complete.  Hope that makes sense.  Below is my current table with the formulas now working and i've confirmed accuracy checking the raw data.

PERSON_NUMBER	Courses	Not Complete 2	Course Not Taken 2
a093	PCI Awareness		PCI Awareness
a093	Política de Antisoborno y Anticorrupción (ABAC)	Política de Antisoborno y Anticorrupción (ABAC)
a098	PCI Awareness	PCI Awareness
a098	Política de Antisoborno y Anticorrupción (ABAC)	Política de Antisoborno y Anticorrupción (ABAC)
a098	Política de la Ley Antimonopolio y de Competencia	Política de la Ley Antimonopolio y de Competencia
a112	PCI Awareness		PCI Awareness
a112	Política de Antisoborno y Anticorrupción	Política de Antisoborno y Anticorrupción (ABAC)
a112	Política de Seguridad de la Información	Política de Seguridad de la Información

[Antonio195754]

@jdbuchanan71 

by the way, i only provided 3 users in the table just as an example.  The population of users is bigger than that.

[jdbuchanan71]

@Antonio195754 

We can combine the logic from the two separate counts like so.

Incomplete 3 = 
VAR _x1 = CALCULATETABLE( VALUES ( Compliance[Course] ), Compliance[Status] = "Incomplete" )
VAR _x2 = VALUES ( Compliance[Course] )

VAR _y1 = CALCULATETABLE ( Courses, INTERSECT ( Courses, _x1 ) )
VAR _y2 = CALCULATETABLE ( Courses, EXCEPT ( Courses, _x2 ) )

VAR _y = UNION ( _y1, _y2 )

VAR _count  = COUNTROWS ( _y )
RETURN
IF ( 
    _count = 0 || NOT HASONEVALUE ( Compliance[PERSON_NUMBER] ), BLANK(),
    CONCATENATEX( _y, Courses[Course], ", ")
)

 This will give the list of Incomplete and Not Take together.

[Antonio195754]

@jdbuchanan71 

JD hello again.  I plugged in your logic below (had to change a few things like "Incomplete" to "Not Complete" and the proper column names) and it took the formula but came back blank when i plugged it in as a column, and when i tried it as a measure, it returned all the courses (9 courses) per user name.  I was hoping the column way would work b/c as you know it gives me the option to do a count of rather than the don't summarize, option, but i got nothing when i changed it to count.  I got 1's when i changed it to distinct count but don't think that's what i'm looking for.  Does the below look correct @jdbuchanan71 . 

Also, what's really weird now is when i try to plug in the incomplete 2 and not taken 2 measures you provided me, back to the table, it no longer shows the table i had replied back with on the previous reply!  It shows every course again per user in both the incomplete 2 and not taken 2 measure. I've included  those below the incomplete measure.  Very weird as my table hasn't changed.  I'm using the user ID column, the course column from the courses table, and then the 2 measures you gave me and it worked last week...  Ultimately that's fine if they don't work b/c technically that wasn't what i was looking for, but just an FYI of what is happening when i try to bring them back in.  

Incomplete 3 =

VAR _x1 = CALCULATETABLE( VALUES ( Compliance[COURSE_NAME] ), Compliance[COURSE_STATUS] = "Not Complete" )

VAR _x2 = VALUES ( Compliance[COURSE_NAME] )

VAR _y1 = CALCULATETABLE ( Courses, INTERSECT ( Courses, _x1 ) )

VAR _y2 = CALCULATETABLE ( Courses, EXCEPT ( Courses, _x2 ) )

VAR _y = UNION ( _y1, _y2 )

VAR _count = COUNTROWS ( _y )

RETURN

IF (

_count = 0 || NOT HASONEVALUE ( Compliance[PERSON_NUMBER] ), BLANK(),

CONCATENATEX( _y, Courses[Courses], ", ")

)

Incomplete 2 =

VAR _x = CALCULATETABLE( VALUES ( Compliance[COURSE_NAME] ), Compliance[COURSE_STATUS] = "Not Complete" )

VAR _y = CALCULATETABLE ( Courses, INTERSECT ( Courses, _x ) )

VAR _count = COUNTROWS ( _y )

RETURN

IF (

_count = 0 || NOT HASONEVALUE ( Compliance[PERSON_NUMBER] ), BLANK(),

CONCATENATEX( _y, Courses[Courses], ", ")

)

Course Not Taken 2 =

VAR _x = VALUES ( Compliance[COURSE_NAME] )

VAR _y = CALCULATETABLE ( Courses, EXCEPT ( Courses, _x ) )

VAR _count = COUNTROWS ( _y )

RETURN

IF (

_count = 0 || NOT HASONEVALUE ( Compliance[PERSON_NUMBER] ), BLANK(),

CONCATENATEX( _y, Courses[Courses], ", ")

)

[Antonio195754]

@jdbuchanan71 

My fault JD, i got all the formulas to work and the incomplete 3 formula is now aggregating the not taken and not complete formulas.  But what it is doing is just showing me the name of the courses missing and it only works when i associate it in a table with the Course name from Course table.  Is there a way you can think of for the incomplete 3 formula to return a count of courses not complete per user without it being in a table format?  If it's not possible then i'll close this question with your solution being accepted but i'm hoping to get the count of not complete per user so that i can use those numbers to get the % complete vs not complete for the overall population of users.

[jdbuchanan71 · Accepted solution]

@Antonio195754 

If you return _count in the measure that should give you what you are looking for.

Incomplete 3 =
VAR _x1 =
    CALCULATETABLE (
        VALUES ( Compliance[COURSE_NAME] ),
        Compliance[COURSE_STATUS] = "Not Complete"
    )
VAR _x2 = VALUES ( Compliance[COURSE_NAME] )
VAR _y1 = CALCULATETABLE ( Courses, INTERSECT ( Courses, _x1 ) )
VAR _y2 = CALCULATETABLE ( Courses, EXCEPT ( Courses, _x2 ) )
VAR _y = UNION ( _y1, _y2 )
VAR _count = COUNTROWS ( _y )
RETURN
    _Count

[Antonio195754]

@jdbuchanan71 

That worked!!!  So do you think this count will work without me having to throw it into a table?  B/c what i'll do with that measure count is basically use it to give me my % of not complete.  I haven't tried it yet but wanted to ask you before i do so.

[Antonio195754]

@jdbuchanan71 

JD what it's doing is letting me only use the measure in a table 😞  If i return only the measure which should give me the count of all not complete, it returns 9; Which is the course count of all the applicable courses.  What's more interesting is that when i have the entire population in the table, it does return the courses not complete per user, but the total at bottom of table is 9 rather than it totalling up all the courses missing.  What do you think @jdbuchanan71?  

[jdbuchanan71]

@Antonio195754 

That is because of the way "totals" are calculated in a matrix.  When looking at a total row the model has no idea about the values on the rows above, all it knows is the filters that are applied to the total row which means it is seeing uncompleted at least once for each course.  Because of all the variables and counts that go into getting the correct amount per employee it would probably be easier to utilize that measure in a further SUMX to get the correct total.

Something like this:

Incomplete With Total = SUMX(VALUES(Compliance[Employee]),[Incomplete 3])

[Antonio195754]

@jdbuchanan71   Sorry forgot to tag you 🙂