Create a new table using columns from other tables

[saxenaa]

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

[v-huizhn-msft · Accepted solution]

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.

I create TableA nad Table, I add index column by click Index Column under Add column. Please see TableA\B with index columns.

Add index column in Query Editor
TableATableB

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

[TomMartens]

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