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!
