Forum Discussion
Helper IUnwanted data repeated in matrix
I am getting data from two sources. One source includes projects, employees, and the number of hours employees are assigned per project. the second source includes projects, employees, and the number of hours employees charged to projects per pay period. The two tables have a many-to-many cardinality, and I have not been able to figure out how to connect the number of hours assigned per employee per project to the number of hours charged per employee per project per pay period. I can either get the total number of hours charged for all employees on the project
Example = Project Name Employee Hours Assigned Hours Charged
Project ABC John Doe 50 125
Jane Smith 75 125
Jo Cooper 100 125
or I can get the individual number of hours charged per employee, but it is copied for each employee with assigned hours
Example = Project Name Employee Hours Assigned Empl_ID Hours Charged
Project ABC John Doe 50 JD1 25
JS1 50
JC1 50
Jane Smith 75 JD1 25
JS1 50
JC1 50
Jo Cooper 100 JD1 25
JS1 50
JC1 50
Any advice or suggestions on how to fix this problem is appreciated.
Thank you,
Shauna
Try a star schema
If you filter by the shared dimension... Employee...you should get both hours assigned and hours charged.
2 Replies
- kentyler
Solution Sage
Try a star schema
If you filter by the shared dimension... Employee...you should get both hours assigned and hours charged.
v-juanli-msftCommunity Support
Hi smstrickland
Agreed with kentyler, here are details how to implement it:
create two tables
employee = DISTINCT(UNION(VALUES(Table1[Employee]),VALUES(Table2[Employee]))) project = DISTINCT(UNION(VALUES(Table1[Project Name]),VALUES(Table2[Project Name])))Create relationships
Finally, add columns into a matrix visual
Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.