Solved: Need help for a DAX Query

Anonymous · ‎07-12-2023

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 😊.

some_bih · ‎07-13-2023

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 5

FILTER (

ADDCOLUMNS ( _qualiefied_table, "@Number Name comp", [Num of comp] ),

[@Number Name comp] = 5

)

RETURN

COUNTROWS ( _filtered_table )

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

View solution in original post

ThxAlot · ‎07-13-2023

comp.pbix

One measure is enough to do the trick

comp.xlsx

For fun only, a showcase of powerful Excel formulas,

Expertise = List.Accumulate(

{Days as from Today},

{Skills and Knowledge},

(Current, Everyday) => Current & Day.LearnAndPractise(Everyday)

)