Merge table using DAX and Create a calculated table

[siva_powerbi]

I have 2 tables, I have merged in Power Query and created a Merged table (Columns from both tables), but I need to do the same using DAX without using power Query.

loaded tables into Power BI and linked the sheets, but unable to create the calcualted tables as the combination of columns from both the tables, I am able to create the calculated table from either of the tables but not from both the tables combined.

Table1                                                           

Test1	Test2
Jobs	1
Jobs1	1

   Table 2

Test3	Test4
1	1
1	1

In power query I have merged tables(Inner Join) and created a new result set

Test1

Test2

Test3

Test4

I pulled resultset to DAX and created calculted table.

But my requirement is link tables in DAX and create a calcualted table. Here I am able to create for one table and not for 2 tables.

Not sure how to merge 2 tables in dax and create a calculated table.

Thanks for help

[v-kelly-msft · Accepted solution]

Hi  @siva_powerbi ,

The .pbix file you share cant be opened;

I use your sample data for instance:

First create a relationship between the 2 tables,

Then create a new table as below:

Table = ADDCOLUMNS('Table1',"Test 3",CALCULATE(MAX('Table2'[Test3]),USERELATIONSHIP(Table1[Test2],Table2[Test3])),"Test 4",CALCULATE(MAX('Table2'[Test4]),USERELATIONSHIP(Table1[Test2],Table2[Test3])))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[siva_powerbi]

@v-Kellu-msft Thanks for the Answer. Will try the solution and accept it as answer, tomorrow. Thanks Siva

[amitchandak]

@siva_powerbi , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

Dax, how to join, merge, etc you can refer: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Appreciate your Kudos.

[siva_powerbi]

@amitchandak Thanks for reply.

Attached files and data source file.

In Power Query file I was able to merge and create a calcualted table in DAX

In DAX though I have linked I am unable to get the calculated table with columns from both the tables,

I have added a simple example to sum the columns but y original calculation is more complex and need to merge 2 tables in DAX.

Please access files from below link as unable to add pbix files in forum.

https://www.dropbox.com/s/ukfj20r4ro82fus/Forum.zip?dl=0