Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Everyone,
I have a complex requirment which i am unable to resolve. Basicall i have 3 data set which is from 3 different source (Tables)
Source 1) Employee Id along with their Job Level
Source 2) Mandatory Training Module for each Job Level
Source 3) List of Employee Id's along with the Job Level who have completed 1 or more training from the Mandatory training Module list.
Date set is attached below.
Requirement is, we are looking for Job Level wise, Mandatory training module wise completion rate. Please can you resolve and share this in PBIX format which will be of more help.
Data set:
Source 1) Employee Id along with their Job Level
Emp Id | Job Level |
100 | 2 |
200 | 3 |
300 | 2 |
400 | 2 |
500 | 3 |
600 | 4 |
700 | 3 |
800 | 3 |
900 | 4 |
1000 | 3 |
1100 | 3 |
1200 | 3 |
1300 | 3 |
1400 | 3 |
1500 | 4 |
Source 2) Mandatory Training Module for each Job Level
JL | Mandatory Training Module |
2 | Foundation - 2 |
2 | Intermediate - 2 |
2 | Pro - 2 |
3 | Foundation - 3 |
3 | Intermediate - 3 |
3 | Pro - 3 |
4 | Foundation - 4 |
4 | Intermediate - 4 |
4 | Pro - 4 |
Source 3) List of Employee Id's along with the Job Level who have completed 1 or more training from the Mandatory training Module list.
Emp Id | JL | Training Module Completed |
100 | 2 | Foundation - 2 |
100 | 2 | Intermediate - 2 |
100 | 2 | Pro - 2 |
200 | 3 | Foundation - 3 |
200 | 3 | Intermediate - 3 |
300 | 2 | Foundation - 2 |
400 | 2 | Foundation - 2 |
400 | 2 | Intermediate - 2 |
500 | 3 | Foundation - 3 |
500 | 3 | Intermediate - 3 |
600 | 4 | Foundation - 4 |
700 | 3 | Foundation - 3 |
800 | 3 | Foundation - 3 |
800 | 3 | Intermediate - 3 |
800 | 3 | Pro - 3 |
900 | 4 | Foundation - 4 |
900 | 4 | Intermediate - 4 |
1000 | 3 | Foundation - 3 |
1000 | 3 | Intermediate - 3 |
1000 | 3 | Pro - 3 |
1100 | 3 | |
1200 | 3 | |
1300 | 3 | |
1400 | 3 | |
1500 | 4 |
Thanks in advance
Solved! Go to Solution.
@shantupm5
Please check the attached file.
Completion Rate =
VAR __EmpJob = COUNT( EmpJob[Emp Id] )
VAR __EmpCount = COUNT( EmpJobModule[Emp Id])
RETURN
DIVIDE(__EmpCount,__EmpJob)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
Based on the 3 tables that you have shared, show the expected result very clearly in a Table format. Still better would be share the download link of the PBI file with 4 tabs - one each for the Tables that yo have shared in your original post and the last one showing the expected result.
Hello Ashish,
Unfortunelty i will not be able to upload the PBIX due to security reasons.
so to illisturate below is the output we need from above table
For Example : For Job Leve 3, we have 9 employees.
So each employee should complete all the mandatory trainings
But for Foundation - 3, only 5 have completed out of 9 employees. which will be 55.6% as mentioned below.
JL | 2 | 3 | 4 |
Foundation - 2 | 100% | ||
Intermediate - 2 | 67.00% | ||
Pro - 2 | 33.33% | ||
Foundation - 3 | 55.60% | ||
Intermediate - 3 | 44.40% | ||
Pro - 3 | 22.20% | ||
Foundation - 4 | 67.00% | ||
Intermediate - 4 | 33.33% | ||
Pro - 4 |
@shantupm5
Please check the attached file.
Completion Rate =
VAR __EmpJob = COUNT( EmpJob[Emp Id] )
VAR __EmpCount = COUNT( EmpJobModule[Emp Id])
RETURN
DIVIDE(__EmpCount,__EmpJob)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |