cancel
Showing results for
Did you mean: Frequent Visitor

## Create a new table using columns from other tables

I am sure this is trvial , but I am not sure how to do this.,

I have table A,B,C,D and I need to create new table Z with selected columns from A,B,C,D

I tried SELECTCOLUMNS but it selects columns from one single table , I tried combining with CALCULATE but its did not worked.

any pointer would be great help.

I would like to do it using dax

1 ACCEPTED SOLUTION  Microsoft

Hi @saxenaa,

I create sample table and reproduce your scenario. You can get expected result by adding index column in A,B,C,D then create Z including index column, to get all select columns using the relation between index. Please review more details as follows. Add index column in Query Editor TableA TableB

2. Get the max index value of A and B, there are 7>4 rows, so I select index in B to create TableZ. Then create relationship between A and Z, B and Z as follows.  3. If I want to select Column1 and Column2 from A, and Column1 from B to create Z, I just create the calculated columns using the formulas in Table Z.

```A.Column1 = RELATED(A[Column1])
A.Column2 = RELATED(A[Column2])
B.Column1 = RELATED(B[Column1])```

You will get the expected result: TableZ

Best Regards,
Angelia

2 REPLIES 2  Microsoft

Hi @saxenaa,

I create sample table and reproduce your scenario. You can get expected result by adding index column in A,B,C,D then create Z including index column, to get all select columns using the relation between index. Please review more details as follows. Add index column in Query Editor TableA TableB

2. Get the max index value of A and B, there are 7>4 rows, so I select index in B to create TableZ. Then create relationship between A and Z, B and Z as follows.  3. If I want to select Column1 and Column2 from A, and Column1 from B to create Z, I just create the calculated columns using the formulas in Table Z.

```A.Column1 = RELATED(A[Column1])
A.Column2 = RELATED(A[Column2])
B.Column1 = RELATED(B[Column1])```

You will get the expected result: TableZ

Best Regards,
Angelia  Super User

Hey,

One solution could be to bind the columns just using CROSSJOIN (...) like so

```Z Table =
CROSSJOIN(VALUES('A item'[A item]), CROSSJOIN(VALUES('B item'[B item]), VALUES('C item'[C item])))```

This creates a table with 3 columns and a number of rows = countrows('A item')*countrows('B item')*countrows('C item')

To be more specific how the new table will be composed you can use something like this

```Z table =
GENERATEALL (
'A item',
VAR AitemBForeignKey = 'A item'[B ForeignKey]
RETURN
SELECTCOLUMNS (
CALCULATETABLE ( 'B item', 'B item'[B item ID] = AitemBForeignKey),
"acolumn", 'B item'[acolumn],
"bcolumn", 'B item'[bcolumn]
)
)```

Here an iterator is created, that iterates over table 'A item' stores a value from a column of the current row in the iterator into a variable and uses this variable to as a filter in the CALCULATETABLE() function.

Hope this gets you started

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany  