Create a new table with columns from 2 separate tables using DAX

Ive been trying to work out how to create a new table with 2 columns - column A from table 1 , column B from table 2.

I want to create a custom table that just has the values I need, and I can modify any data as I need to without touching our main tables. I want to be able to drag any columns from the new table into a graph so I can build custom graphs without having measure or dimension incompatability.

I tried using ALL but it has to be the same table. I'm frustrated as it just seems to be elusive to solve.....surely DAX isnt supposed to be this hard? If it was SQL i would have it done in 30 seconds

Any help is much appreciated.

DAX Command

Anonymous
1 year ago
Hi Anonymous ,

would it please be possible to show the steps to make a copy of the tables inside powerbi , and then add indexes to those temp tables please and then use them?

If you have a column in your table that can be sorted, you can use "Rankx" to create a calculated column. This is the main method of creating an index column using dax.

RANKX function (DAX) - DAX | Microsoft Learn

read-only data warehouse

Do you mean warehouse in Fabric? What is your current connection mode? Change the connection mode to "import" to create index columns in Power Query. "Import" mode will create a copy of the data, and read-only mode has no effect.

Best Regards,
Wenbin Zhou

7 Replies

shafiz_p
Super User
1 year ago
Hi Anonymous It is better to use power query to join/merge multiple tables. However you could use dax NATURALINNERJOIN function to join multiple table. You need a common key.

If you are interested you could try the below code:
NewTable = SELECTCOLUMNS( NATURALINNERJOIN(NATURALINNERJOIN(Category, Data), NewCat), "ID", Category[ID], "Categ", Category[Category], "DataID", Data[Value], "DewCate", NewCat[Category] )

Try to join all three table and select columns from all 3 tables. See images of table:

Output:

Hope this helps!!
If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz
Anonymous
Not applicable
1 year ago
Hi,

I may not be able to join on the tables as they are unrelated tables, is there a different way I could try it please?

It might be partially working but I get an error :

"A table of multiple values was supplied where a single value was expected"

- we do get duplicate values in the columns due to the nature of the data.

I was able to get it to work using a visual calculation on the table - I subtract one date column from another date column and get the difference between the 2 as a 3rd column, but when I try and create a graph, it takes across all 3 columns, but I only want 2 columns.

I'm not trying to be difficult, its just proving to be more difficult than I thought 🙂
Kedar_Pande
Super User
1 year ago
Anonymous
CustomTable =
SELECTCOLUMNS(
NATURALINNERJOIN(
Table1,
Table2
),
"ColumnA", Table1[ColumnA],
"ColumnB", Table2[ColumnB]
)
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
- Anonymous
  Not applicable
  1 year ago
  Hi
  
  Unfortunately I dont have a key between the two tables to use for a join.
  
  I can use this to code below generate a single combined date column that gets named as "LD"
  ( Note we have tables with spaces in the names, hence the need for quotes ) :
  
  EVALUATE
  VAR A =
  SELECTCOLUMNS ('table 1', "LD", 'table 1'[date 1])
  VAR B =
  SELECTCOLUMNS ('table 2',"LD2", 'table 2'[date 2])
  VAR Result =
  UNION(A,B)
  RETURN
  Result
  
  but I really want a single table with the two columns LD and LD2 side by side in the same table.
  
  I noticed we might be able to use SELECTCOLUMNS, but you need to do something to the columns like multiplying it by someting etc , but I dont need that, just the raw columns as it is.
  
  And can I run this in a measure or do I need to run some other way?
  - Anonymous
    Not applicable
    1 year ago
    Hi all,thanks for your quick response, I'll add more.
    
    Hi Anonymous ,
    
    You mentioned that there is no associated column between the two tables. And you need to join the two tables horizontally. It may not be possible to achieve your needs through DAX. I think you need to use Power Query to create an index column for the two tables and then perform the merge operation based on the index column.
    
    Best regards