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

[wokka]

Hi

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.

[Kedar_Pande]

@wokka 

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

[wokka]

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]

Hi all,thanks for your quick response, I'll add more.

Hi @wokka ,

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

[wokka]

Hi v-zhouwen,

As the tables are part of our existing read-only data warehouse, I may not be able to add the indexes to them.

So, 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? That would be incredibly helpful.   I have a SQL DBA background so understand things from a SQL Server & temp tables persepctive.

Ive been kind of dumped in the deep end with powerbi, its a steep learning curve.

Thank you for your help so far. 😊

[Anonymous · Accepted solution]

Hi @wokka ,

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

[wokka]

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   🙂

[shafiz_p]

Hi @wokka  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