Forum Discussion

PBIC's avatar
PBIC
Frequent Visitor
6 years ago
Solved

Lookup value from another table with multi-column Join

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

 

Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    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.

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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.

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    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.