Lookup value from another table with multi-column Join

[PBIC]

I am trying to sum a column from another table on a row by row basis.

Table1
Ref	Date
1	03/09/2019
1	04/09/2019
2	05/09/2019
3	06/09/2019

Table2
Ref	Date	Value
1	03/09/2019	1
1	03/09/2019	3
1	04/09/2019	5
2	05/09/2019	7
3	06/09/2019	9

I want to end up with the Table2 Value column summed on Table1 as a new column called NewValue:

Table1
Ref	Date	NewValue
1	03/09/2019	4
1	04/09/2019	5
2	05/09/2019	7
3	06/09/2019	9

I cannot have a relationship as there is more than one column in the join. I need to add a new column (actually there are three of these to do but I'll replicate the solution three times).

I have tried with CALCULATE and SUMX and VLOOKUP but cannot figure out the code.

Please can somebody suggest the code for the new column.

Thanks

[v-joesh-msft · Accepted solution]

Hi @PBIC ,

You can try the following methods:

1. Create a new column in Table(1):

ColumnT1 = 'Table (1)'[Ref]&'Table (1)'[Date]

2. Create a new column in Table(2):

ColumnT2 = 'Table (2)'[Ref]&'Table (2)'[Date]

3. Establish a relationship between two tables based on ColumnT1 and ColumnT2:

4. Create a new calculated column in Table(1):

NewValue = CALCULATE(SUM('Table (2)'[Value]))

The results are as follows:

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@PBIC as you already figured out that you cannot set relationship because two columns are involved, you can create a key column by concatenative date and ref column on both the table and then set the relationship on that. Once it is done, it will be pretty straight forward.