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.

shortname/nodename(Table1) can have values from shortname or nodename from table2.  so the condition to check needs to be done.

 

Final result is value to be made available in Table2. 

 

sample pbix is avaialble in the below link with data if anyone wants to try.

 

https://drive.google.com/file/d/16YFu-BEKLHR4tnmhrLbodjJf7EOJvk9u/view?usp=sharing

 

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

     

7 Replies

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    This calculated column formula works

    Column = IFERROR(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.

     

  • ak77's avatar
    ak77
    Post Patron
    2 years ago

    Ashish_Mathur , The value column of Table2 should have 2 and 3 from Table1 

     

    My apologies for wrong values in description.. please let me know if it is possible 

     

     

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      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.

       

  • ak77's avatar
    ak77
    Post Patron
    2 years ago

    Ashish_Mathur , thanks for reply.

     

    This works perfectly when import mode of data is used. but unfortunately i am using direct mode(huge amout of data led us to this decision) and this DAX is failing . 

     

    is there a way to achieve this using direct query. Please let me know