Need help for a DAX Query

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

[some_bih · Accepted solution]

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 )

[ThxAlot · Accepted solution]

comp.pbix

One measure is enough to do the trick

comp.xlsx

For fun only, a showcase of powerful Excel formulas,

[some_bih · Accepted solution]

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 )