Parent-Child relationship problem

[ambient_88]

Hello Guys,

I have a problem I've been trying to tackle but to no avail.

Problem:

An employee can be transferred to a different supervisor in the middle of the month.

5/1 - 5/15			5/16 - 5/31
Employee A	Supervisor A		Employee A	Supervisor B
Employee B	Supervisor A		Employee B	Supervisor B
Employee C	Supervisor A		Employee C	Supervisor B

In the table above, Employees A,B,C were assigned to Supervisor A from 5/1 - 5/15. All their stats should be retained under Supervisor A. From 5/16 - 5/31, they were assigned to Supervisor B. Likewise, all their stats from then on should fall under Supervisor B. 

I can't seem to find the solution to this. Right now, I have the relationships set up like so:

dim_Roster		dim_Calendar
EmpID		Date
Name
Supervisor
fact_Data
EmpID
Date

The way I have it set up right now does not take into account supervisor changes within the month. I've looked into many-to-many relationships using a bridge table but still can't get it to work.

Appreciate any assistance. Thank you.

[v-yuezhe-msft]

@ambient_88,

Please share sample data of the above three tables and post expected result based on the sample data here.

Regards,
Lydia

[ambient_88]

Hello,

Please see the sample data in the link below:

https://drive.google.com/open?id=1w9h1X_irjQJAlCNJyDvDgLqO9zcZGZwn

Thanks is advance!

[Ashish_Mathur]

Hi,

I think i can help here.  However, i notice that there is no Year in the data range of column D.  Do you not get the year from the source?  

[ambient_88]

Hello,

I should be able to get the year date for the column D.

Thanks!

[Ashish_Mathur]

Hi,

Please share the exact date format in that case.  Infact, share the link from where i can download your Excel file and show the expected result there in a Table format.

[ambient_88]

Hello,

Here's the link for the sample data. 

https://drive.google.com/open?id=1w9h1X_irjQJAlCNJyDvDgLqO9zcZGZwn

As you can see, a person can be moved to a different supervisor within the month. With the way I have it set up right now, if a person moves to another team within the month, his/her score will transfer as well. We don't want that. The score under Supervisor A should stay under that supervisor.

I would appreciate if you can guide me as to how to design the data model for this. Right now I have two lookup tables (Employee and Date) and one data table (survey data).

Thank you.

[Ashish_Mathur]

Hi,

Please refer to my solution in this PBI file.

Hope this helps.