Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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:
Best Regards,
Angelia
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.
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:
Best Regards,
Angelia
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |