Get values from 3 tables into one matrix

[kattlees]

I need to get the sum of three columns from two different tables and can't figure out why I'm struggling with it.

Tables are: Employees

EmployeeID	Location
MINN0001	Loc1
MINN0001	Loc2
SMIT0001	Loc1
JONE0001	Loc1

Table 2: Amt Pd:

EmployeeID	Location	Color	Date	AmtPd
MINN0001	Loc1	Green	12/31/21	5.00
SMIT0001	Loc1	Green	12/31/21	5.00
MINN0001	Loc1	Blue	12/31/21	10.00
SMIT0001	Loc1	Blue	12/31/21	8.00
JONE0001	Loc1	Green	12/31/21	12.00
MINN0001	Loc2	Blue	12/31/21	7.00

Table3: Amt Earned

EmployeeID	Location	Date	Color	AmtEarned	AmtEr
MINN0001	Loc1	12/5/21	Blue	2.00	1.00
MINN0001	Loc1	12/17/21	Blue	3.00	1.00
MINN0001	Loc1	12/5/21	Green	7.00	5.00
SMIT0001	Loc1	12/17/21	Blue	3.00	1.00
SMIT0001	Loc1	12/5/21	Green	5.00	2.00
JONE0001	Loc1	12/5/21	Blue	0	5.00

Result I need is a matrix: (Choosing Blue and Loc1)

EmployeeID	Location	Date	Pd	Earned	Er
MINN0001	Loc1	12/31/21	10.00	5.00	2.00
SMIT0001	Loc1	12/31/21	8.00	3.00	1.00
JONE0001	Loc1	12/31/21	0.00	0.00	5.00

Result I need is a matrix: (Choosing Green and Loc1)

EmployeeID	Location	Date	Pd	Earned	Er
MINN0001	Loc1	12/31/21	5.00	7.00	5.00
SMIT0001	Loc1	12/31/21	5.00	5.00	2.00
JONE0001	Loc1	12/31/21	12.00	0.00	0.00

I know I can need to get the end of the month from the paid column but for the life of me I can't figure out how to do the relationships to get the matrix options I need.

[v-jingzhang · Accepted solution]

Hi @kattlees 

I add EmployeeLoc columns to all three tables by combining EmployeeID and Location columns. And build relationships on these new columns. Also I add dim tables for colors and locations. Download the attachment below to see details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[smpa01]

@kattlees  create separate dimension tables for Location and Color and model the data accordingly. Then bring slicers from those tables.