Forum Discussion
Employee Training Status
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' ) - completedTrainingsThe model and the measures allow to create a simple visual like the one below:
Hopefully, this helps to tackle your challenge.Regards,
Tom
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
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!