topic Re: Need help for a DAX Query in DAX Commands and Tips

Need help for a DAX Query

Anonymous — Thu, 13 Jul 2023 03:19:49 GMT

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

Re: Need help for a DAX Query

some_bih — Thu, 13 Jul 2023 07:45:06 GMT

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 )

Re: Need help for a DAX Query

ThxAlot — Thu, 13 Jul 2023 12:40:41 GMT

comp.pbix

One measure is enough to do the trick

comp.xlsx

For fun only, a showcase of powerful Excel formulas,