Need help for a DAX Query in Power Bi to count the Competent people

[Anonymous]

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 🙂

.

[Ahmedx]

pls try this

Measure = 
VAR _t1 =
    SUMMARIZE ( FILTER(
        'New New FINAL',([Job Code],[Level]) in {("QC-01","Qualified")}),
        'New New FINAL'[EmployeeId],
        "Count",
            CALCULATE (
                DISTINCTCOUNT ( 'New New FINAL'[Name competency] ),
                ALLEXCEPT ( 'New New FINAL', 'New New FINAL'[EmployeeId] )
            )
    )
RETURN
    COUNTROWS ( FILTER ( _t1, [Count] = 5 ) )

[Anonymous]

Hello @ryan_mayu , Thank you for the swift response. I had a look at the solution, It was great but my main aim is to validate each employee with each Competency name because the competency names keep changing every year. so It would be great if you can suggest me a solution where we can validate Employee against Competency name.

That's the reason I wanted to do as the below Query. 

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"
)

  Also, This query is working fine when I use or (||) in the place of and (&&) 
As competency Values are in a Single column the && operator is not working. this was what I figured out during the process.

[ryan_mayu]

@Anonymous 

does that mean you need to update the DAX every year due to the change of compentency names?

[ryan_mayu]

@Anonymous 

maybe you can try this

Measure = 
var tbl=SUMMARIZE(FILTER('Table','Table'[Level]="Qualified"),'Table'[EmployeeId],"competency",DISTINCTCOUNT('Table'[Name competency]))
return COUNTROWS(FILTER(tbl,[competency]=5))

pls see the attachment below