Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hello,
Please see the sample data in the link below:
https://drive.google.com/open?id=1w9h1X_irjQJAlCNJyDvDgLqO9zcZGZwn
Thanks is advance!
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?
Hello,
I should be able to get the year date for the column D.
Thanks!
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.
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.
Hi,
Please refer to my solution in this PBI file.
Hope this helps.
@ambient_88,
Please share sample data of the above three tables and post expected result based on the sample data here.
Regards,
Lydia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |