I am trying to develop a method which creates the following relationship between two tables. So, that even when an employee changes. The new employees name replaces the previous person who held that job title. So, as a business we have an idea how much cost is assigned to each workbase and how many hours are covered within that workbase on a week-to-week basis.
Table 1) The INPUT: I have created a JSON form which essentially enables those creating the staffing plans the ability to select from various options (with an effective from date). If at any time it changes. A new date is entered which superseeds the old submission(s).
Workbase = Multiple sites.
Shift Plan = What area within the site (the list available depends on the workbase selected).
Effective Date = as explained above.
Job Title = list of 33 different job titles within the company which can be assigned to multiple.
Level = The grade level's that are available are those that are assigned to Job Title.
Default Contracted Weekly Hours = As per contractually employed.
Core Day Hours = string of numbers from 0.5 Hours (being half an hour) to 1 being a full hour (up to 40) The same process also applies to 'Extra Day Hours', 'Core Night Hours', and 'Extra Night Hours'.
Table 2) The OUTPUT: In Power Bi, I'd like to assign names from our dynamic staff list to each job title. That way, we can see who works at each workbase and how many hours in each particular week (effective from date) they are doing.
I have created a relationship between the columns called Job Title (from the form) and job_title (from the dynamic staff list), but I have been unable to say, 'this person, works at this workbase as a Cleaner'.
Where we have NO MATCH between job titles. I'd like it to create a vacancy so that our recruitment team can search for vacnacies by the number of hours and job title.
The report would also then enable payroll to know how much they are spending (per week) or by whatever period selected.
If anybody has any ideas. I would really value them.
