Data Modeling

Hi nchamilton2 ,

Thank you for reaching out to Microsoft Fabric Community.

To model this scenario effectively, create a unified table called CombinedAssignments in Power Query. Start by taking the ProjectUserAssigned table, and add a column named AssignmentType with the value "Project" and a TaskID column set to null. Then, take the ProjectTaskUserAssigned table, merge it with the ProjectTask table to bring in the related ProjectID, and add an AssignmentType column with the value "Task". Ensure both datasets have the same structure: EmployeeID, ProjectID, TaskID, and AssignmentType, and append them together into the new CombinedAssignments table.

Next, in the data model, create many-to-one relationships from CombinedAssignments to Employees (via EmployeeID), to Projects (via ProjectID), and to ProjectTask (via TaskID). This combined approach allows you to pull in employee details from the Employees table and clearly identify whether the assignment is at the project or task level using the AssignmentType column.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!