Table Join conditional(multiple columns)

[ak77]

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

[Ashish_Mathur · Accepted solution]

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 · Accepted solution]

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]

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

Thanks again. 

[ak77]

@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]

Sorry i would not know.

[ak77]

@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 · Accepted solution]

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.

[Ashish_Mathur]

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.