Forum Discussion

pang22's avatar
pang22
Icon for Helper III rankHelper III
3 years ago
Solved

Write DAX to combine 2 tables

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's avatar
    Jihwan_Kim
    3 years ago

    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

     

     

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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