Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BenArnold
Frequent Visitor

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

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 RefManagerCourse NameStatus
ANoHealth and Safety    Complete
BNoHealth and Safety    Complete
CYesHealth and Safety    Not Complete
DYesHealth and Safety    Not Complete
ENoHealth and Safety    Not Complete
ANoRecruitment    Not Complete
BNoRecruitment    Not Complete
CYesRecruitment    Complete
DYesRecruitment    Complete
ENoRecruitmentNot Complete
ANoInformation Security    Complete
BNoInformation Security    Complete
CYesInformation Security    Complete
DYesInformation Security    Not Complete
ENoInformation 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):

CourseMandatory for Employees?Mandatory for managers?
Health and Safety

Yes

Yes
RecruitmentNoYes
Information SecurityYesYes

 

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

1 ACCEPTED SOLUTION
barritown
Super User
Super User

Hi @BenArnold,

For example, you could you use such a measure:

 

barritown_0-1686313341649.png

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

View solution in original post

4 REPLIES 4
BenArnold
Frequent Visitor

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?

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
Super User
Super User

Hi @BenArnold,

For example, you could you use such a measure:

 

barritown_0-1686313341649.png

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

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

 

Ben

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors