Forum Discussion
identify values based on selection criterias
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,
1 Reply
- amitchandak
Super User
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
