Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
@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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |