Forum Discussion
pang22
Helper III
Helper IIICreate new table using DAX from 2 tables with different number columns
Hello,
I have 2 tables (Table 1 with 6 columns & Table 2 with 5 columns) loaded in PowerBI desktop.
How to write a DAX to create a new table to sum the qty by month, factory, area and type in PowerBI? Thanks
I used the UNION but the PowerBI said the number of columns are different and cannot combine togehter.
Table 1
| Order Qty | Factory | Loading month | Type | Area | Cost |
| 3 | QN | 2023/11 | A | China | 2 |
| 5 | QN | 2023/11 | A | China | 5 |
| 1 | QB | 2023/12 | A | HK | 6 |
| 2 | QB | 2023/12 | A | HK | 2 |
Table 2
| Balance Qty | Suggested Factory | Year&Month | Type | Area |
| 2 | QB | 2023/11 | B | HK |
| 3 | QN | 2023/12 | B | HK |
| 5 | QN | 2023/12 | B | China |
| 4 | QB | 2023/11 | B | China |
Desire outcome: Combine Table 1 & 2 with below 5 columns only, sum the "Order Qty" & "Balance Qty" as "Combine Qty"
| Month | Combine Factory | Area | Type | Combine Qty |
| 2023/11 | QN | China | A | 8 |
| 2023/12 | QB | HK | A | 3 |
| 2023/11 | QB | HK | B | 2 |
| 2023/11 | QB | China | B | 4 |
| 2023/12 | QN | HK | B | 3 |
| 2023/12 | QN | China | B | 5 |
hi pang22 is this your desired outcome? Maybe I specified the sequence of columns incorrectly, but the concept of joining two tables is as follows
t1ANDt2 = VAR t1 = SUMMARIZE('Table 1','Table 1'[Loading month],'Table 1'[Type],'Table 1'[Factory],'Table 1'[Area],"qty", SUM('Table 1'[Order Qty])) var t2 = SUMMARIZE('Table 2','Table 2'[Year&Month],'Table 2'[Type],'Table 2'[Suggested Factory],'Table 2'[Area], "qty", SUM('Table 2'[Balance Qty])) VAR t3 = UNION(t1,t2) return t3
1 Reply
DimaMDSolution Sage
hi pang22 is this your desired outcome? Maybe I specified the sequence of columns incorrectly, but the concept of joining two tables is as follows
t1ANDt2 = VAR t1 = SUMMARIZE('Table 1','Table 1'[Loading month],'Table 1'[Type],'Table 1'[Factory],'Table 1'[Area],"qty", SUM('Table 1'[Order Qty])) var t2 = SUMMARIZE('Table 2','Table 2'[Year&Month],'Table 2'[Type],'Table 2'[Suggested Factory],'Table 2'[Area], "qty", SUM('Table 2'[Balance Qty])) VAR t3 = UNION(t1,t2) return t3