cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ambient_88 Frequent Visitor
Frequent Visitor

Parent-Child relationship problem

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 ASupervisor A Employee ASupervisor B
Employee BSupervisor A Employee BSupervisor B
Employee CSupervisor A Employee CSupervisor 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.

7 REPLIES 7
Moderator v-yuezhe-msft
Moderator

Re: Parent-Child relationship problem

@ambient_88,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ambient_88 Frequent Visitor
Frequent Visitor

Re: Parent-Child relationship problem

Hello,

 

Please see the sample data in the link below:

 

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

 

 

Thanks is advance!

Super User
Super User

Re: Parent-Child relationship problem

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?  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ambient_88 Frequent Visitor
Frequent Visitor

Re: Parent-Child relationship problem

Hello,

 

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

 

Thanks!

Super User
Super User

Re: Parent-Child relationship problem

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
ambient_88 Frequent Visitor
Frequent Visitor

Re: Parent-Child relationship problem

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.

Sample_data_screenshot.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

Super User
Super User

Re: Parent-Child relationship problem

Hi,

 

Please refer to my solution in this PBI file.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,154)