Write DAX to combine 2 tables

[pang22]

Hello,

I have 2 tables (Table 1 & 2) loaded in PowerBI desktop.

How to write a DAX to create a new table to sum the qty by loading month and by factory in PowerBI? Thanks

Table 1                                                                                      Table 2

Col name: Order Qty, Factory, Loading month                        Col name: Balance Qty, Suggested Factory, Laoding month

                                 

Desired outcome, Combine Table 1 & 2:

Col name: Loading month, Combine Factory, Combine Qty

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table.

Combined table =
VAR _t =
    SUMMARIZE (
        SELECTCOLUMNS (
            UNION ( Table1, Table2 ),
            "Loading month", Table1[Loading month],
            "Combine Factory", Table1[Factory]
        ),
        [Loading month],
        [Combine Factory]
    )
VAR _addqty =
    ADDCOLUMNS (
        _t,
        "Combine Qty",
            SUMX (
                FILTER (
                    Table1,
                    Table1[Factory] = EARLIER ( [Combine Factory] )
                        && [Loading month] = EARLIER ( [Loading month] )
                ),
                Table1[Order Qty]
            )
                + SUMX (
                    FILTER (
                        Table2,
                        Table2[Suggested Factory] = EARLIER ( [Combine Factory] )
                            && Table2[Loading month] = EARLIER ( [Loading month] )
                    ),
                    Table2[Balance Qty]
                )
    )
RETURN
    _addqty