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/
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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 447 members 4,014 guests
Please welcome our newest community members: