Forum Discussion
Eligiblity Matrix DAX
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.
Here 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"
)
)
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,...)
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.