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

     

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Sorry i would not know.