In my data model I have two facts table, every table has own date field. I need to create chart that should show data from each table on one time axes. Originally those tables was linked by Item - Warehouse key, but when I created link table I found that in this case I will have incorrect data because one Item-Warehouse combination can have more than one row with data, so I created key based on three field Warehouse, Item and Date
Item Warehouse Quantity Item Warehouse Date Quantity
1111 US1 10 1111 US1 20170401 2
1111 US1 20170402 5
1111 US1 20170311 3
Link table also contains type(table1 or table2 ) and calendar.
My problem: when I use such key I can build necessary chart , but this key cut part of data from second table.
Dimension is Transaction and I want to show sum of Table2.Quantity. If I use ItemWarehouse I see all data, but if I use itemWarehouse Date I see only piece of data