Forum Discussion

ak77's avatar
ak77
Post Patron
2 years ago
Solved

Table Join conditional(multiple columns)

Hi all, Need a help on the below scenario . please check and help if possible   i have Table 1 and Table 2 to be joined based on 2 fixed columns( sequence and code) and 1 conditional column....
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    This calculated column formula works

    Column = coalesce(CALCULATE(SUM(Table1[value]),FILTER(Table1,Table1[sequence]=EARLIER(Table2[sequence])&&Table1[code]=EARLIER(Table2[code])&&Table1[shortname/nodename]=EARLIER(Table2[shortname]))),CALCULATE(SUM(Table1[value]),FILTER(Table1,Table1[sequence]=EARLIER(Table2[sequence])&&Table1[code]=EARLIER(Table2[code])&&Table1[shortname/nodename]=EARLIER(Table2[nodename]))))

    Hope this helps.

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi ak77 ,
    Thanks for Ashish_Mathur  reply.

    Based on the data you provide, you can first create relationships based on the sequence you want to pass, and then create a calculated column in Table 2

    Value = 
IF(
    'Table 2'[shortname] = RELATED('Table 1'[shortname/nodename]) || 'Table 2'[nodename] = RELATED('Table 1'[shortname/nodename]),
    RELATED('Table 1'[value])
)

    Final output

     

    Best regards,
    Albert He

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi ak77 ,
Thanks for Ashish_Mathur  reply.

Based on the data you provide, you can first create relationships based on the sequence you want to pass, and then create a calculated column in Table 2

Value = 
IF(
    'Table 2'[shortname] = RELATED('Table 1'[shortname/nodename]) || 'Table 2'[nodename] = RELATED('Table 1'[shortname/nodename]),
    RELATED('Table 1'[value])
)

Final output

 

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

  • ak77's avatar
    ak77
    Post Patron
    2 years ago

    Thanks Anonymous  and Ashish_Mathur , The solution u both provided helped in resolving the issue while using import and direct mode 

     

    Thanks again.