Data Modeling

[nchamilton2]

Can someone help me model the following please?
I have a Projects Table, ProjectTask Table, Employee Table, a ProjectUserAssigned Table, and a ProjectTaskUserAssigned Table. 

The Projects Table has a one to many connection to ProjectTask. The ProjectUserAssigned is connected to Projects via a many to One. The ProjectTaskUserAsssigned is conected to ProjectTask via a many to one. I want to connect the Employee Table to both the ProjectUserAssigned and ProjectTaskUserAssigned to get employee details so I can show all employees whether they are assigned a project or a projecttask. If a projectTask can have more than one ProjectTaskAssignment. How do I incorporate the Userelationship function to pull employee detail from the employee table for ProjectTaskuserAssignments? Is there a better approach than described above?

Thanks,

[v-sathmakuri]

Hi @nchamilton2 ,

I hope the information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri]

Hi @nchamilton2 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!

[v-sathmakuri]

Hi @nchamilton2 ,

May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[v-sathmakuri · Accepted solution]

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!!

[Ritaf1983]

Hi @nchamilton2 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523