How to write DAX to join two tables on multiple columns in Power bi?

[Deeintu]

Hi All,

I have data in 2 tables, I need to join these 2 tables on multiple columns by writing a DAX formula.

These 2 tables don't have connection or relationship in the data model. These are independent tables.

Also I don't want to merge these tables in Power query. My requirement is to achieve through DAX.

How can i get  Table 2 details along with corresponding sales values in Table 1 by joining on multiple columns.

Join conditions will be

Table1.Year =  Table2.Year
Table1.Category =  Table2.SubGroup
Table1.Region =  Table2.Location
Table1.Product =  Table2.Product

Table 1 data :

Table 2:

Expected result just showing few rows but ideally need to show  for all possible  rows.

Please see below links of  attached data and pbi files.

PBIX file:
https://www.dropbox.com/s/y9k3glcsmztxdqx/Sample%20File.pbix?dl=0

Table 1:
https://www.dropbox.com/scl/fi/33u4ma5hccjwq26w5b039/Sample-Table-1.xls?dl=0&rlkey=r3qvvq7rg8lr6preh...

Table 2:
https://www.dropbox.com/scl/fi/wcfz9vsttvmua3ghmil1o/Sample-Table-2.xls?dl=0&rlkey=206oaq0lnosh39dst...

Thanks in advance
Dee

[ryan_mayu · Accepted solution]

@Deeintu 

you can try this

Column = maxx(FILTER('Table 1','Table 1'[Year]='Table 2'[Year]&&'Table 2'[Subgroup]='Table 1'[Category]&&'Table 1'[Location]='Table 2'[Location]&&'Table 1'[Product]='Table 2'[Product]),'Table 1'[Sales])

[jimbobobwalker]

This isn't working for I have the same columns in two tables I have tried the formula with and without quotes

With qoutes still give errors

Column = maxx(FILTER('RER_SUMMARY','RER_SUMMARY[Partner]'='OB_SUMMARY[Partner]'&&'RER_SUMMARY[FedACH ProcessDate]'='OB_SUMMARY[FedACH ProcessDate]'))

[ryan_mayu]

what's the error message? 

and you missed the second parameter.

https://learn.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008-r2/ee634576(v=sql.105)?wt.mc...

[jimbobobwalker]

Oh man didn't even see that, work great thank you.

[ryan_mayu]

you are welcome

[Deeintu]

Great it worked.
Thanks @ryan_mayu 

[ryan_mayu]

you are welcome

[VijayP]

@Deeintu 
you can use UNION Function 
Union ( 
Summarize (Table 1, required columns in speicific order),
Summarise ( Table 2, required columns same speicific order above)
Both Table columns order should be same