Forum Discussion
Helper IIIEligiblity 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.
Helper IJust 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,...)
Helper IIIThis 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 below
The 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.