Forum Discussion

saxenaa's avatar
saxenaa
Frequent Visitor
9 years ago
Solved

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

 

 

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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