Employee Training Status

[PRock]

Hello everyone. Bottom line up front, I am putting together an employee training dashboard to show training status for each employee. I have the data for each courses completed by each employee, but there are required training that employees have not completed. I manually created a new table that list all of the required courses and trying to figure out the best way to tackle this for my Dashboard using Power BI.

For example:

Data pulled from learning center:

The required training/course list:

Then I want to be able to add "Complete" and "Incomplete" for each training an employee has completed.

Please assist and thank you!

All the best,

[Streakenw]

One way to streamline the process is by automating the comparison of completed vs. required courses using calculated columns or measures. This would let you mark each training as "Complete" or "Incomplete" based on course completion.

[SPAC]

Hello Tom,

This is perfect and thank you!

I need adjust certain values from the report I pull and will incorporate the following measures you've shared. I will also figure out how I am going to lookup or match the courses/training since I manually created the required training because the actual completed training is pull from a learning center system. This way, I won't have to manually clean up the pulled report every time I update the dashboard.

[TomMartens · Accepted solution]

Hey @PRock ,

I created a little pbix file that you can find here:

I followed the principles of dimensional modeling and created a star schema: Simple Model - Training.pbix

I recommend reading this article about dimensional modeling and the importance of start schemas for Power BI: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema?WT.mc_id=DP-MVP-5003068

I created two measures "" and "".

The first one is counting how many trainings an Emp ID has completed:

completed Trainings = COUNTROWS( 'Completed Trainings' ) 

and the second measure counts the number of trainings an Emp ID has still to complete based on the number of rows in the table "Required Trainings":

open Trainings = 
var currentEmp = SELECTEDVALUE( 'Employees'[Emp ID] )
var completedTrainings = CALCULATE( [completed Trainings] , 'Completed Trainings'[Emp ID] = currentEmp )
return

COUNTROWS( 'Required Trainings' ) - completedTrainings

The model and the measures allow to create a simple visual like the one below:

Hopefully, this helps to tackle your challenge.

Regards,

Tom

[SPAC]

Hello once again Tom. I love what you've added above added solution and I will definitely use it.

To further explain (I hope I am making sense). The following is how I want my Matrix visual to look like. I created a merged as new query for the two tables using "Full Outer" (table1 with the list of completed training and table 2 is the one I manually created for the required training).

Please see the following of how I want the visual "Matrix" to look like:

EMPLOYEE	Intro to Policy	Sexual Harrassment	Equal Opportunity	Ethics	Total Incomplete
Employee 1	Completed	blank	Not Completed	blank	3
Employee 2	blank	blank	Completed	blank	3
Employee 3	Completed	Completed	Completed	blank	1
Total Incomplete	1	2	1	3

Thank you once again Tom!

All the best,

PRock!