Training Eligibility Calculated Column

[shantupm5]

Good day!

My goal is to determine employee eligibility for training based on their job level and industry defined in Eligiblity Matrix. I have three tables: 'Eligibility Matrix', 'Employee Database', and 'Training Completion Report'.

I require a new calculated column in the 'Employee Database' that flags an employee as 'Eligible' if they've met the training criteria outlined in the 'Eligibility Matrix'.

The eligibility rules vary by training type:

• If the 'Training Type' is 'Digital', the employee must have completed all trainings within any one of the given combinations (e.g., if 'VBA, POWER BI' is listed, they need both).

• If the 'Training Type' is 'Domain', the employee needs to have completed at least one of the listed trainings."

will update data in the reply as its not allowing me since exceeded the characters

---

[v-tsaipranay]

Hi @shantupm5 ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @shantupm5 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[v-tsaipranay]

Hi @shantupm5 ,

I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.

Thank you.

[v-tsaipranay · Accepted solution]

Hi @shantupm5 ,

Thank you for reaching out to the Microsoft fabric community forum.

Since the eligibility logic especially for Digital Training, where an employee must complete all trainings from at least one valid combination is too complex for a calculated column, Power Query is the most effective approach.

Here's how you can implement it step-by-step: First, split the training combinations in the Eligibility Matrix into individual rows, and assign each combination a unique ID. Then, merge this with the Employee Database using Job Level and Industry to match each employee to their applicable combinations.

Next, join this with the Training Completion Report to identify which required trainings each employee has completed. By grouping and comparing the number of trainings completed against the total required per combination, you can identify valid completions.

Finally, aggregate at the employee level to flag whether they’ve completed any full combination, and merge this result back to the Employee Database table to mark each employee as "Eligible" or "Not Eligible".

Let me know if you'd like to apply the same logic for Domain Training as well, or if you'd like a sample PBIX for reference.

Thank you.

[shantupm5]

Eligiblity Matrix: 

Industry	Job Level	Training Type	Training
Generic	3	Digital Training	Advanced Excel ,VBA
Generic	3	Digital Training	Advanced Excel ,Python
Generic	3	Digital Training	Advanced Excel ,R Programming
Generic	3	Digital Training	Advanced Excel , R200 SQL
Generic	3	Digital Training	Advanced Excel ,Power BI
Generic	3	Digital Training	Advanced Excel ,Tableau
Generic	3	Digital Training	Advanced Excel ,Business Analyst
Generic	3	Digital Training	Advanced Excel ,MSBI
Generic	3	Digital Training	VBA ,Advanced Excel
Generic	3	Digital Training	VBA ,Python
Generic	3	Digital Training	VBA ,R Programming
Generic	3	Digital Training	VBA , R200 SQL
Generic	3	Digital Training	VBA ,Power BI
Generic	3	Digital Training	VBA ,Tableau
Generic	3	Digital Training	VBA ,Business Analyst
Generic	3	Digital Training	VBA ,MSBI
Generic	3	Digital Training	Python ,Advanced Excel
Generic	3	Digital Training	Python ,VBA
Generic	3	Digital Training	Python ,R Programming
Generic	3	Digital Training	Python , R200 SQL
Generic	3	Digital Training	Python ,Power BI
Generic	3	Digital Training	Python ,Tableau
Generic	3	Digital Training	Python ,Business Analyst
Generic	3	Digital Training	R Programming ,Advanced Excel
Generic	3	Digital Training	R Programming ,VBA
Generic	3	Digital Training	R Programming ,Python
Generic	3	Digital Training	R Programming , R200 SQL
Generic	3	Digital Training	R Programming ,Power BI
Generic	3	Digital Training	R Programming ,Tableau
Generic	3	Digital Training	R Programming ,Business Analyst
Generic	3	Digital Training	R Programming ,MSBI
Generic	3	Digital Training	R200 SQL ,Advanced Excel
Generic	3	Digital Training	R200 SQL ,VBA
Generic	3	Digital Training	R200 SQL ,Python
Generic	3	Digital Training	R200 SQL ,R Programming
Generic	3	Digital Training	R200 SQL ,Power BI
Generic	3	Digital Training	R200 SQL ,Tableau
Generic	3	Digital Training	R200 SQL ,Business Analyst
Generic	3	Digital Training	R200 SQL ,MSBI
Generic	3	Digital Training	Power BI ,Advanced Excel
Generic	3	Digital Training	Power BI ,VBA
Generic	3	Digital Training	Power BI ,Python
Generic	3	Digital Training	Power BI ,R Programming
Generic	3	Digital Training	Power BI , R200 SQL
Generic	3	Digital Training	Power BI ,Tableau
Generic	3	Digital Training	Power BI ,Business Analyst
Generic	3	Digital Training	Power BI ,MSBI
Generic	3	Digital Training	Tableau ,Advanced Excel
Generic	3	Digital Training	Tableau ,VBA
Generic	3	Digital Training	Tableau ,Python
Generic	3	Digital Training	Tableau ,R Programming
Generic	3	Digital Training	Tableau , R200 SQL

[shantupm5]

Training Completion Report

Emp No	Trainings Completed
641	VBA
638	VBA
506	VBA
534	VBA
357	VBA
276	VBA
319	VBA
556	VBA
551	VBA
670	VBA
262	VBA
456	VBA
552	VBA
669	VBA
183	VBA
420	VBA
437	VBA
120	VBA
220	VBA
171	T300 - Advanced Supply Chain Management
157	T300 - Advanced Supply Chain Management
596	T300 - Advanced Supply Chain Management
602	T300 - Advanced Supply Chain Management
142	T300 - Advanced Supply Chain Management
185	T300 - Advanced Supply Chain Management
388	T300 - Advanced Supply Chain Management
497	T300 - Advanced Supply Chain Management
553	T300 - Advanced Supply Chain Management
161	T300 - Advanced Supply Chain Management
151	T300 - Advanced Supply Chain Management
513	T300 - Advanced Supply Chain Management
470	T300 - Advanced Supply Chain Management
544	T300 - Advanced Supply Chain Management
144	T300 - Advanced Supply Chain Management
669	T300 - Advanced Supply Chain Management
468	T300 - Advanced Supply Chain Management
549	T300 - Advanced Supply Chain Management
537	T300 - Advanced Supply Chain Management
159	T300 - Advanced Supply Chain Management
421	T300 - Advanced Supply Chain Management
207	T300 - Advanced Supply Chain Management
598	T300 - Advanced Supply Chain Management
515	T300 - Advanced Supply Chain Management
602	T300 - Advanced Supply Chain Management
605	T300 - Advanced Supply Chain Management
557	T300 - Advanced Supply Chain Management
552	T300 - Advanced Supply Chain Management
522	T300 - Advanced Supply Chain Management
550	T300 - Advanced Supply Chain Management
547	T300 - Advanced Supply Chain Management
499	T300 - Advanced Supply Chain Management
147	T300 - Advanced Supply Chain Management
549	T300 - Advanced Supply Chain Management
602	T200 - Supply Chain Management Workshop
13	T200 - Supply Chain Management Workshop
610	T200 - Supply Chain Management Workshop
501	T200 - Supply Chain Management Workshop
4	T200 - Supply Chain Management Workshop
6	T200 - Supply Chain Management Workshop
6	T200 - Supply Chain Management Workshop
570	T200 - Supply Chain Management Workshop
558	T200 - Supply Chain Management Workshop
189	T200 - Supply Chain Management Workshop
497	T200 - Supply Chain Management Workshop
9	T200 - Supply Chain Management Workshop
483	T200 - Supply Chain Management Workshop
503	T200 - Supply Chain Management Workshop
236	T200 - Supply Chain Management Workshop
221	T200 - Supply Chain Management Workshop
506	T200 - Supply Chain Management Workshop
612	T200 - Supply Chain Management Workshop
643	T200 - Supply Chain Management Workshop
560	T200 - Supply Chain Management Workshop
171	T200 - Supply Chain Management Workshop
10	T200 - Supply Chain Management Workshop
154	T200 - Supply Chain Management Workshop
196	T200 - Supply Chain Management Workshop
236	T200 - Supply Chain Management Workshop
368	T200 - Supply Chain Management Workshop
623	T200 - Supply Chain Management Workshop
222	T200 - Supply Chain Management Workshop
100	T200 - Supply Chain Management Workshop
8	T200 - Supply Chain Management Workshop
670	T200 - Supply Chain Management Workshop
274	T200 - Supply Chain Management Workshop
708	T200 - SCM in Telecom Industry
477	T200 - SCM in Telecom Industry
267	T200 - SCM in Telecom Industry
535	T200 - SCM in Telecom Industry
374	T200 - SCM in Telecom Industry
445	T200 - SCM in Telecom Industry
534	T200 - SCM in Telecom Industry
713	T200 - SCM in Telecom Industry
538	T200 - SCM in Telecom Industry
484	T200 - SCM in Telecom Industry
595	T200 - SCM in Telecom Industry
420	T200 - SCM in Telecom Industry
376	T200 - SCM in Telecom Industry
375	T200 - SCM in Telecom Industry
640	T200 - SCM in Software Industry
671	T200 - SCM in Software Industry
672	T200 - SCM in Software Industry
468	T200 - SCM in Software Industry
436	T200 - SCM in Software Industry
156	T200 - Logistics Management
549	T200 - Logistics Management
495	T200 - Logistics Management
264	T200 - Logistics Management
551	T200 - Logistics Management
261	T200 - Logistics Management
552	T200 - Logistics Management
262	T200 - Logistics Management
549	T200 - Logistics Management
256	T200 - Logistics Management
258	T200 - Logistics Management
550	T200 - Logistics Management
548	T200 - Logistics Management
554	T200 - Logistics Management
556	T200 - Logistics Management
557	T200 - Logistics Management
260	T200 - Logistics Management
555	T200 - Logistics Management
254	T200 - Logistics Management
259	T200 - Logistics Management
255	T200 - Logistics Management
385	T100 - Order Management in Telecom Industry
431	T100 - Order Management in Telecom Industry
544	T100 - Order Management in Telecom Industry
535	T100 - Order Management in Telecom Industry
477	T100 - Order Management in Telecom Industry
481	T100 - Order Management in Telecom Industry
514	T100 - Order Management in Software Industry
207	T100 - Order Management in Software Industry
512	T100 - Order Management in Software Industry
167	T100 - Order Management in Software Industry
366	T100 - Order Management in Software Industry
150	T100 - Order Management in Software Industry
13	T100 - Order Management in Software Industry
149	T100 - Order Management in Software Industry
432	T100 - Order Management in Software Industry
433	T100 - Order Management in Software Industry
674	T100 - Order Management in Software Industry
421	T100 - Order Management in Software Industry
468	T100 - Order Management in Software Industry
704	T100 - Order Management in Software Industry
474	T100 - Order Management in Software Industry
227	T100 - Order Management in Software Industry
268	T100 - Order Management in Software Industry
267	T100 - Order Management in Software Industry
284	T100 - Order Management in QTC
468	T100 - Order Management in QTC
240	T100 - Order Management in QTC
495	T100 - Logistics Management
324	T100 - Logistics Management
552	T100 - Logistics Management
549	T100 - Logistics Management
550	T100 - Logistics Management
49	T100 - Logistics Management
254	T100 - Logistics Management
556	T100 - Logistics Management
256	T100 - Logistics Management
258	T100 - Logistics Management
360	T100 - Logistics Management
297	T100 - Logistics Management
260	T100 - Logistics Management
555	T100 - Logistics Management
262	T100 - Logistics Management
556	T100 - Logistics Management
557	T100 - Logistics Management
407	T100 - Logistics Management
231	T100 - Logistics Management
460	T100 - Logistics Management
463	T100 - Logistics Management
256	T100 - Logistics Management
302	T100 - Generic
672	T100 - Generic
207	T100 - Generic
218	T100 - Generic
30	T100 - Generic
35	T100 - Generic
604	T100 - Generic
95	T100 - Generic
208	T100 - Generic
388	T100 - Generic
388	T100 - Generic
311	T100 - Generic
503	T100 - Generic
556	T100 - Generic
601	T100 - Generic
10	T100 - Generic
160	T100 - Generic
4	T100 - Generic
6	T100 - Generic
218	T100 - Generic
241	T100 - Generic
366	T100 - Generic
110	T100 - Generic
151	T100 - Generic
510	T100 - Generic
658	T100 - Generic
386	T100 - Generic
612	T100 - Generic
421	T100 - Generic
468	T100 - Generic
487	T100 - Generic
714	T100 - Generic
395	T100 - Generic
373	T100 - Generic
231	T100 - Generic
408	T100 - Generic
638	Excel
658	Excel
465	Excel
480	Excel
717	Excel
548	Excel
599	Excel
314	Excel
311	Excel
670	Excel
552	Excel
456	Excel
520	Excel
628	Excel
408	Excel
399	Excel
528	Excel
546	Excel
550	Excel
644	Excel
668	Excel
704	Excel
314	Excel
715	Excel
420	Excel
180	Excel
685	Excel
392	Excel
386	Excel
687	Excel

[shantupm5]

Employee DataBase: 

EmpNo	Job Level	Industry
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
140	4	Generic
141	4	Generic
142	4	Generic
143	4	Generic
144	4	Generic
145	4	Generic
146	4	Generic
147	4	Generic
254	3	Logistics
255	3	Logistics
256	3	Logistics
257	3	Logistics
258	3	Logistics
259	3	Logistics
260	3	Logistics
261	3	Logistics
262	3	Logistics
263	3	Logistics
264	3	Logistics
265	3	Logistics
266	3	Logistics
267	3	Telecom
268	3	Telecom
269	3	Telecom
370	3	Telecom
371	4	Telecom
372	3	Telecom
373	3	Telecom
374	3	Telecom
375	3	Telecom
376	3	Telecom
377	3	Telecom
378	3	Telecom
379	3	Telecom
380	3	Telecom
381	3	Telecom
388	4	Telecom
399	4	Telecom
421	4	Telecom
432	4	Telecom
468	4	Telecom
469	4	Telecom
470	4	Telecom
475	4	Telecom
483	4	Telecom
548	4	Logistics
549	4	Logistics
550	4	Logistics
551	3	Logistics
552	4	Logistics
553	4	Logistics
554	3	Logistics
555	3	Logistics
556	3	Logistics
557	4	Logistics
599	4	Logistics
600	3	Logistics