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

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.