Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pang22
Helper III
Helper III

Create 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 QtyFactoryLoading monthTypeAreaCost
3QN2023/11AChina2
5QN2023/11AChina5
1QB2023/12AHK6
2QB2023/12AHK2

 

Table 2

Balance QtySuggested FactoryYear&MonthTypeArea
2QB2023/11BHK
3QN2023/12BHK
5QN2023/12BChina
4QB2023/11BChina

 

 Desire outcome: Combine Table 1 & 2 with below 5 columns only, sum the "Order Qty" & "Balance Qty" as "Combine Qty" 

MonthCombine FactoryAreaTypeCombine Qty
2023/11QNChinaA8
2023/12QBHKA3
2023/11QBHKB2
2023/11QBChinaB4
2023/12QNHKB3
2023/12QNChinaB5

 

 

 

 

pang22_0-1697699076833.png

 

1 ACCEPTED SOLUTION
DimaMD
Solution Sage
Solution 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

 

 

DimaMD_0-1697703459710.png

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

View solution in original post

1 REPLY 1
DimaMD
Solution Sage
Solution 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

 

 

DimaMD_0-1697703459710.png

 


__________________________________________

Thank you for your like and decision

__________________________________________

Greetings from Ukraine

To help me grow PayPal: embirddima@gmail.com

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.