Unwanted data repeated in matrix

[smstrickland]

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

[v-juanli-msft]

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
Maggie

Community 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.

[kentyler · Accepted solution]

Try a star schema 

If you filter by the shared dimension... Employee...you should get both hours assigned and hours charged.