How to count the number of people who have completed mandatory training

[BenArnold]

Hello,

I am looking for a DAX measure to do the following:

I have a table of employees, their manager status, and the completion status of multiple training courses. I want to be able to count the number of employees (not managers) who have completed the two mandatory training courses. Here's a simplified table (Table 1):

Person Ref	Manager	Course Name	Status
A	No	Health and Safety	Complete
B	No	Health and Safety	Complete
C	Yes	Health and Safety	Not Complete
D	Yes	Health and Safety	Not Complete
E	No	Health and Safety	Not Complete
A	No	Recruitment	Not Complete
B	No	Recruitment	Not Complete
C	Yes	Recruitment	Complete
D	Yes	Recruitment	Complete
E	No	Recruitment	Not Complete
A	No	Information Security	Complete
B	No	Information Security	Complete
C	Yes	Information Security	Complete
D	Yes	Information Security	Not Complete
E	No	Information Security	Complete

For non-managers (i.e. "No" in the second column) they need to have completed "Health and Safety" and "Information Security".  I keep this information in a second table (Table 2):

Course	Mandatory for Employees?	Mandatory for managers?
Health and Safety	Yes	Yes
Recruitment	No	Yes
Information Security	Yes	Yes

In this example, employees A,B and E are not managers, so those are the ones I'm interested in. Employees A and B have completed both the mandatory courses, so the answer I want to get is 2.

In reality I will have several months' data in table 1, so I will be able to track the completion numbers over time. I'll have an additional column titled "month". Not included that above as I didn't want to over-complicate it, but thought I'd mention it in case it is important.

Any help would be greatly appreciated.

Many thanks

Ben

[BenArnold]

Thank you very much. This definitely works.

Is there a way to make it a bit more flexible? i.e. where I could change the value in "Mandatory for Employees?" field in table 2 to "Yes" or "No" and this would update the count?

[barritown]

Yup, please try the measure below (not sure it is the optimal one, but it should work).

The assumption here is that the tables are linked ( Table2[Course] 1-* Table1[Course Name] ).

Employees in Compliance = 
VAR EtalonCount = COUNTROWS ( FILTER ( Table2, [Mandatory for Employees?] = "Yes" ) )
VAR _tbl = CALCULATETABLE ( Table1, Table2[Mandatory for Employees?] = "Yes" , Table1[Manager] = "No", Table1[Status] = "Complete" )
RETURN COUNTROWS ( 
            FILTER ( 
                ADDCOLUMNS ( 
                    SUMMARIZE ( _tbl, [Person Ref] ),
                    "Flag", 
                    VAR CurrentPerson = [Person Ref]
                    RETURN COUNTX ( FILTER ( _tbl, [Person Ref] = CurrentPerson ), [Course Name] ) = EtalonCount ), 
                [Flag] = TRUE () ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[barritown · Accepted solution]

Hi @BenArnold,

For example, you could you use such a measure:

In plain text:

Employees in Compliance = 
VAR _HS = DISTINCT ( SELECTCOLUMNS ( FILTER ( 'fact', [Manager] = "No" && [Status] = "Complete" && [Course Name] = "Health and Safety" ), "Person Ref", [Person Ref] ) )
VAR _IS = DISTINCT ( SELECTCOLUMNS ( FILTER ( 'fact', [Manager] = "No" && [Status] = "Complete" && [Course Name] = "Information Security" ), "Person Ref", [Person Ref] ) )
RETURN COUNTX ( INTERSECT ( _HS, _IS ), [Person Ref] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[BenArnold]

Genuis!  Thank you so much. That was the route I had attempted to take, but hadn't managed to make it work.

Ben