Forum Discussion
Need help for a DAX Query
Hello Community,
I am trying to find the count of employees who are fully competent.
You can say an employee is fully competent when,
For Distinct Job code, For Distinct Site the employee should complete all the Competencies(Reviews,Risks,deployment,services,agile) and the level should be Qualified.
QualifiedEmployees =
CALCULATE (
COUNTROWS ( 'New New FINAL' ),
'New New FINAL'[Job Code] = "QC-01",
'New New FINAL'[Level] = "Qualified",
'New New FINAL'[EmployeeId] IN VALUES ( 'New New FINAL'[EmployeeId] ),
'New New FINAL'[Name competency] = "Reviews" &&
'New New FINAL'[Name competency] = "Risks" &&
'New New FINAL'[Name competency] = "deployment" &&
'New New FINAL'[Name competency] = "Services" &&
'New New FINAL'[Name competency] = "Agile"
)
New New Final Table (Example)
| EmployeeId | Job Code | Level | Name competency |
|------------|----------|-----------|-----------------|
| 1 | QC-01 | Qualified | Reviews |
| 1 | QC-01 | Qualified | Risks |
| 1 | QC-01 | Qualified | deployment |
| 1 | QC-01 | Qualified | Services |
| 1 | QC-01 | Qualified | Agile |
| 2 | QC-01 | Qualified | Reviews |
| 2 | QC-01 | Qualified | Risks |
| 2 | QC-01 | Qualified | deployment |
| 2 | QC-01 | Qualified | Services |
| 3 | QC-01 | Qualified | Reviews |
| 4 | QC-01 | Qualified | Reviews |
| 4 | QC-01 | Qualified | Services |
| 5 | QC-01 | Qualified | Reviews |
| 5 | QC-01 | Qualified | deployment |
So based on the DAX query written The Output should be Count of employees who has complted all the competencies
Therefore in this case its 1. because only person has complted all the 5 competencies
Please help me with this issue
Thanks 😊.
Hi Anonymous possible solution, not totaly eleagant but I think is working.
Create two measures: Num of comp and # Employes qualified, adjust Sheet2 to your table name
Assumption: number of commpetence is 5 to count employess.
Did I answer your question? Mark my post as a solution! Kudos Appreciated!
Num of comp =DISTINCTCOUNT ( Sheet2[Name competency] )# Employes qualified =VAR _qualiefied_table =SUMMARIZE ( FILTER ( Sheet2, Sheet2[Level] = "Qualified" ), Sheet2[EmployeeId] )VAR _filtered_table =//number of competencies set to 5FILTER (ADDCOLUMNS ( _qualiefied_table, "@Number Name comp", [Num of comp] ),[@Number Name comp] = 5)RETURNCOUNTROWS ( _filtered_table )
2 Replies
Hi Anonymous possible solution, not totaly eleagant but I think is working.
Create two measures: Num of comp and # Employes qualified, adjust Sheet2 to your table name
Assumption: number of commpetence is 5 to count employess.
Did I answer your question? Mark my post as a solution! Kudos Appreciated!
Num of comp =DISTINCTCOUNT ( Sheet2[Name competency] )# Employes qualified =VAR _qualiefied_table =SUMMARIZE ( FILTER ( Sheet2, Sheet2[Level] = "Qualified" ), Sheet2[EmployeeId] )VAR _filtered_table =//number of competencies set to 5FILTER (ADDCOLUMNS ( _qualiefied_table, "@Number Name comp", [Num of comp] ),[@Number Name comp] = 5)RETURNCOUNTROWS ( _filtered_table )