Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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...
DAX Command
  • Anonymous's avatar
    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

shafiz_p's avatar
shafiz_p
Icon for Super User rankSuper 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