identify values based on selection criterias

[Anonymous]

Dear community,
I have a list of recruits for whom I know the hiring date, geographical location and business unit they will be working for.

Job ID	Job Location	Job Type	Target Start Date	Starter Class Recommended ID	Advanced Class Recommended ID
Engineer 1	RUS	Wells	15/01/2020
Engineer 2	NAM	Drilling	24/01/2020
Engineer 3	MEA	Testing	20/01/2020
Technician 1	EAF	Wells	15/01/2020
Technician 2	MEA	Wells	17/01/2020
Technician 3	NAM	Measures	01/02/2020
Mechanic 1	RUS	Testing	30/01/2020
Mechanic 2	NAM	Testing	01/02/2020
Mechanic 3	EAF	Drilling	20/01/2020
Mechanic 4	MEA	Drilling	22/01/2020

I need to assign those candidates to 2 training classes: starter & advanced.
parameters to be taken into consideration:
for starter class:

• the class starting date should closely match the hiring date.
• the class location should match the candidate location

Starter Class ID	School Name	School Location	Class start Date	Class End Date
1	Center A	RUS	15/01/2020	25/01/2020
2	Center B	NAM	26/01/2020	05/02/2020
3	Center C	MEA	20/01/2020	30/01/2020
4	Center D	EAF	15/01/2020	25/01/2020
5	Center A	RUS	30/01/2020	09/02/2020
6	Center B	NAM	02/02/2020	12/02/2020
7	Center C	MEA	22/01/2020	01/02/2020
8	Center D	EAF	21/01/2020	31/01/2020

advanced class:

• should begin after the completion of starter class
• should match the candidate business unit
• should match the location of starter class (unless no class available, then class from other location can be proposed)

Advanced Class ID	School Name	School Location	Class Type	Class start Date	Class end date
A	Center A	RUS	Wells	01/02/2020	05/02/2020
B	Center B	NAM	Drilling	06/02/2020	11/02/2020
C	Center C	MEA	Testing	01/02/2020	05/02/2020
D	Center D	EAF	Wells	01/02/2020	05/02/2020
E	Center A	RUS	Drilling	04/02/2020	09/02/2020
F	Center B	NAM	Measures	13/02/2020	18/02/2020
G	Center C	MEA	Wells	05/02/2020	09/02/2020
H	Center D	EAF	Drilling	01/02/2020	05/02/2020
I	Center A	RUS	Testing	11/02/2020	16/02/2020
J	Center B	NAM	Testing	15/02/2020	20/02/2020
K	Center C	MEA	Drilling	05/02/2020	05/02/2020

I would like the PowerBI to suggest the class ID values based on the above. Which set of formulas shall I consider and apply?
thank you for your guidance,

[amitchandak]

Please find the solution at: https://www.dropbox.com/s/pzwdebam0kxbgdi/TrainingBasicAdvance.pbix?dl=0

I wanted to combine Advance class id and Alternate Advance class id, But as of now left them as is.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin