Forum Discussion
Helper IIIEligiblity Matrix DAX
Hello Team
I am stuck with the DAX for one of the validations im performing
I have 3 tables as updated in below tables
- Employee DataBase – With columns 'Employee DataBase'[EmpNo], 'Employee DataBase'[JL] and 'Employee DataBase'[Industry Mapping]
- Training Completion Report – with columns Emp No and Trainings Completed
- Eligiblity Matrix – with columns Competency, Industry, Job Level, Training Type, Training
Basically the Eligibility Matrix is qualifies an employee if they have completed all the relevant trainings based on the Matrix
For Ex: Job level 3, Industry – Generic, the employee should complete 3 levels of competency which is Beginner, Comptent and Proficient. And each competency the employee has to complete the trainings under Mandatory, Domain training and Digital trainings grid.
And for each competency this is the criteria:
For Competent (All Job Levels):
Domain Trainings: ✅ Eligible if any one training from the defined set is completed.
Mandatory Trainings: ✅ Eligible if any one training from the defined set is completed.
Digital Trainings: ✅ Eligible if any one training from the defined set are completed.
For Proficient (All Job Levels):
Digital Trainings: ✅ Eligible if all trainings from the defined set are completed.
Domain Trainings: ✅ Eligible if any one training from the defined set is completed.
For some reason im not able to achieve the desired results. I am failing at line level validation for each competency. Please can you help rectify the DAX or provide a new one? Thanks
Eligiblity Matrix:
Comptency | Industry | Job Level | Training Type | Training |
Proficient | Generic | 3 | Digital Training | Advanced Excel , R200 SQL |
Proficient | Generic | 3 | Digital Training | Advanced Excel ,Power BI |
Proficient | Logistics | 3 | Digital Training | Advanced Excel , R200 SQL |
Proficient | Logistics | 3 | Digital Training | Advanced Excel ,Power BI |
Proficient | Generic | 4 | Digital Training | Advanced Excel , R200 SQL |
Proficient | Generic | 4 | Digital Training | Advanced Excel ,Power BI |
Proficient | Logistics | 4 | Digital Training | Advanced Excel , R200 SQL |
Proficient | Logistics | 4 | Digital Training | Advanced Excel ,Power BI |
Proficient | Generic | 3 | Domain Trainings | T200 - SCM in Software Industry , T200 - SCM in Telecom Industry |
Proficient | Generic | 4 | Domain Trainings | T200 - SCM in Software Industry , T200 - SCM in Telecom Industry |
Proficient | Logistics | 3 | Domain Trainings | Logistics Industry |
Proficient | Logistics | 4 | Domain Trainings | Logistics Industry |
Beginner | Generic | 3 | Domain Trainings | T100 - Generic |
Competent | Generic | 3 | Domain Trainings | T100 - Order Management in Software Industry , T100 - Order Management in Telecom Industry |
Beginner | Logistics | 3 | Domain Trainings | T100 - Logistics Management |
Beginner | Logistics | 4 | Domain Trainings | T100 - Logistics Management |
Competent | Logistics | 3 | Domain Trainings | T200 - Logistics Management |
Beginner | Generic | 4 | Domain Trainings | T100 - Generic |
Competent | Generic | 4 | Domain Trainings | T100 - Order Management in Software Industry , T100 - Order Management in Telecom Industry |
Competent | Logistics | 4 | Domain Trainings | T200 - Logistics Management |
Competent | Generic | 3 | Mandatory | T200 - Supply Chain Management Workshop |
Competent | Logistics | 3 | Mandatory | T200 - Logistics Management |
Competent | Generic | 4 | Mandatory | T300 - Advanced Supply Chain Management |
Competent | Logistics | 4 | Mandatory | T300 - Advanced Supply Chain Management |
Trainings Completed:
Emp No | Trainings Completed |
13 | VBA |
30 | T200 - Supply Chain Management Workshop |
7 | T200 - Supply Chain Management Workshop |
13 | T200 - Supply Chain Management Workshop |
4 | T200 - Supply Chain Management Workshop |
6 | T200 - Supply Chain Management Workshop |
6 | T200 - Supply Chain Management Workshop |
36 | T200 - Supply Chain Management Workshop |
12 | T200 - Supply Chain Management Workshop |
12 | T200 - Supply Chain Management Workshop |
39 | T200 - Supply Chain Management Workshop |
39 | T200 - Supply Chain Management Workshop |
47 | T200 - Supply Chain Management Workshop |
26 | T200 - Supply Chain Management Workshop |
26 | T200 - Supply Chain Management Workshop |
35 | T200 - Supply Chain Management Workshop |
19 | T200 - Supply Chain Management Workshop |
28 | T200 - Supply Chain Management Workshop |
3 | T200 - Supply Chain Management Workshop |
17 | T200 - Supply Chain Management Workshop |
23 | T200 - Supply Chain Management Workshop |
49 | T200 - Supply Chain Management Workshop |
24 | T200 - Supply Chain Management Workshop |
18 | T200 - Supply Chain Management Workshop |
9 | T200 - Supply Chain Management Workshop |
2 | T200 - Supply Chain Management Workshop |
22 | T200 - Supply Chain Management Workshop |
15 | T200 - Supply Chain Management Workshop |
10 | T200 - Supply Chain Management Workshop |
25 | T200 - Supply Chain Management Workshop |
14 | T200 - Supply Chain Management Workshop |
40 | T200 - Supply Chain Management Workshop |
38 | T200 - Supply Chain Management Workshop |
8 | T200 - Supply Chain Management Workshop |
5 | T200 - Supply Chain Management Workshop |
21 | T200 - Supply Chain Management Workshop |
28 | T200 - Supply Chain Management Workshop |
31 | T200 - Supply Chain Management Workshop |
16 | T200 - Supply Chain Management Workshop |
44 | T200 - Supply Chain Management Workshop |
20 | T200 - Supply Chain Management Workshop |
42 | T200 - Supply Chain Management Workshop |
1 | T200 - Supply Chain Management Workshop |
13 | T200 - Supply Chain Management Workshop |
27 | T200 - Supply Chain Management Workshop |
20 | T200 - SCM in Software Industry |
13 | T100 - Order Management in Software Industry |
10 | T100 - Order Management in Software Industry |
13 | T100 - Order Management in Software Industry |
13 | T100 - Order Management in Software Industry |
30 | T100 - Logistics Management |
Employee Data Base
EmpNo | JL | Industry Mapping |
1 | 3 | Generic |
2 | 3 | Generic |
3 | 3 | Generic |
4 | 3 | Generic |
5 | 3 | Generic |
6 | 3 | Generic |
7 | 3 | Generic |
8 | 3 | Generic |
9 | 3 | Generic |
10 | 3 | Generic |
11 | 3 | Generic |
12 | 3 | Generic |
13 | 3 | Generic |
14 | 3 | Generic |
15 | 3 | Generic |
16 | 3 | Generic |
17 | 3 | Generic |
18 | 3 | Generic |
19 | 3 | Generic |
20 | 3 | Generic |
21 | 3 | Generic |
22 | 3 | Generic |
23 | 3 | Generic |
24 | 3 | Generic |
25 | 3 | Generic |
26 | 3 | Generic |
27 | 3 | Generic |
28 | 3 | Generic |
29 | 3 | Generic |
30 | 3 | Generic |
31 | 3 | Generic |
32 | 3 | Generic |
33 | 3 | Generic |
34 | 3 | Generic |
35 | 3 | Generic |
36 | 3 | Generic |
37 | 3 | Generic |
38 | 3 | Generic |
39 | 3 | Generic |
40 | 3 | Generic |
41 | 3 | Generic |
42 | 3 | Generic |
43 | 3 | Generic |
44 | 3 | Generic |
45 | 3 | Generic |
46 | 3 | Generic |
47 | 3 | Generic |
48 | 3 | Generic |
49 | 3 | Generic |
50 | 3 | Generic |
Hi shantupm5 ,
Thank you for your thorough explanation and follow-up. Re-examining the logic after identifying discrepancies is the right approach, particularly with the training validation changes following the update.
Your point about SWITCH(TRUE(), ...) being preferable for handling multiple complex conditions is well taken, and while your current approach is functional, the core challenge is deeper specifically, how training completion is validated within the eligibility matrix.
Currently, the DAX evaluates each training type independently across the entire matrix. However, for Competent and Proficient competencies, the requirements must be validated within the same row of the matrix. This is crucial because:
- Competent requires at least one Digital, one Mandatory, and one Domain training from the same row (matching competency, job level, and industry).
- Proficient requires completion of the entire Digital Training set in a single row, plus one Domain training from that same row.
As it stands, the logic aggregates training types across all rows, which can result in eligibility being incorrectly validated. To resolve this, group and evaluate trainings by each matrix row and check the combinations as defined. This approach ensures employee completions are accurately matched to the required combinations in the matrix.
Thank you.
8 Replies
shantupm5Here is the DAX I used:
DAX Test =
VAR EmpID = 'Employee DataBase'[EmpNo]
VAR JL = 'Employee DataBase'[JL]
VAR Industry = 'Employee DataBase'[Industry Mapping]-- Trainings completed by employee (cleaned)
VAR CompletedTrainings =
CALCULATETABLE(
VALUES('Training Completion Report'[Trainings Completed]),
'Training Completion Report'[Emp No] = EmpID
)-- Filter eligibility matrix for matching job level and industry
VAR MatrixFiltered =
FILTER(
'Eligiblity Matrix',
'Eligiblity Matrix'[Job Level] = JL &&
'Eligiblity Matrix'[Industry] = Industry
)-- Evaluate eligibility per row
VAR RowEligibility =
ADDCOLUMNS(
MatrixFiltered,
"EligibilityScore",
VAR TrainingType = [Training Type]
VAR TrainingList =
SELECTCOLUMNS(
GENERATESERIES(1, PATHLENGTH(SUBSTITUTE([Training], ",", "|")), 1),
"TrainingItem", PATHITEM(SUBSTITUTE([Training], ",", "|"), [Value], TEXT)
)
VAR CompletedList =
SELECTCOLUMNS(CompletedTrainings, "TrainingItem", [Trainings Completed])
RETURN
SWITCH(
TRUE(),
TrainingType = "Domain Trainings", INT(COUNTROWS(INTERSECT(TrainingList, CompletedList)) >= 1),
TrainingType = "Mandatory", INT(COUNTROWS(INTERSECT(TrainingList, CompletedList)) >= 1),
TrainingType = "Digital Training", INT(COUNTROWS(EXCEPT(TrainingList, CompletedList)) = 0),
0
)
)-- Aggregate eligibility per competency
VAR CompetencyEligibility =
GROUPBY(
RowEligibility,
[Comptency],
"EligibilityScore",
MAXX(CURRENTGROUP(), [EligibilityScore])
)-- Separate eligible and ineligible competencies
VAR Eligible =
SELECTCOLUMNS(FILTER(CompetencyEligibility, [EligibilityScore] = 1), "Competency", [Comptency])VAR Ineligible =
SELECTCOLUMNS(FILTER(CompetencyEligibility, [EligibilityScore] = 0), "Competency", [Comptency])
-- Final output
RETURN
IF(
COUNTROWS(Eligible) > 0,
"Eligible - " & CONCATENATEX(Eligible, [Competency], ", "),
IF(
COUNTROWS(Ineligible) > 0,
"Ineligible - " & CONCATENATEX(Ineligible, [Competency], ", "),
"No Matching Criteria"
)
)
OimatHelper I
Just quickly overseeing your code, here is a small thing you can improve
SWITCH(
TRUE(),
TrainingType = "Domain Trainings", INT(COUNTROWS(INTERSECT(TrainingList, CompletedList)) >= 1),
TrainingType = "Mandatory", INT(COUNTROWS(INTERSECT(TrainingList, CompletedList)) >= 1),
TrainingType = "Digital Training", INT(COUNTROWS(EXCEPT(TrainingList, CompletedList)) = 0),
0
)
)Your are misusing SWITCH. The TRUE() trick is pertinent if your have multiple conditions. Here, you only care about the value of TrainingType, so you can directly write SWITCH(TrainingType,"Domain Trainings"....)
The trick is usefull for example : SWITCH(TRUE(), TrainingType = "Mandatory" && Date.[Year] = 2025,...)
- shantupm5
This DAX I created earlier is not pulling the results accurately after i updated
The logis is to applied from all the training type listed under each Competency.
there is also Job level and industry under which the Comptency & Training Types are matached as belowThe competency logic should be applied as follows:
Beginner: Completion of any one Domain Training listed in the respective row.
Competent: Completion of any one Digital Training, any one Domain Training, and any one Mandatory Training listed in the same row.
Proficient: Completion of any one valid combination of Digital Trainings (e.g., Advanced Excel & VBA) listed together in a single row under each training type, along with any one Domain Training from the same row.
Hi shantupm5 ,
Thank you Oimat for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you for your understanding!Hi shantupm5 ,
Thank you for your thorough explanation and follow-up. Re-examining the logic after identifying discrepancies is the right approach, particularly with the training validation changes following the update.
Your point about SWITCH(TRUE(), ...) being preferable for handling multiple complex conditions is well taken, and while your current approach is functional, the core challenge is deeper specifically, how training completion is validated within the eligibility matrix.
Currently, the DAX evaluates each training type independently across the entire matrix. However, for Competent and Proficient competencies, the requirements must be validated within the same row of the matrix. This is crucial because:
- Competent requires at least one Digital, one Mandatory, and one Domain training from the same row (matching competency, job level, and industry).
- Proficient requires completion of the entire Digital Training set in a single row, plus one Domain training from that same row.
As it stands, the logic aggregates training types across all rows, which can result in eligibility being incorrectly validated. To resolve this, group and evaluate trainings by each matrix row and check the combinations as defined. This approach ensures employee completions are accurately matched to the required combinations in the matrix.
Thank you.
Hi shantupm5 ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.
Thank you.