Forum Discussion

mim's avatar
mim
Advocate V
9 years ago
Solved

left outer join using dax, Multiple to Multiple

I have two tables in my data model, currently,  i am exporting them to Excel do the merge there using PQ and import back to PowerBI Data model, as you would imagine, this is not efficient.   i have...
Need Help
  • TomMartens's avatar
    TomMartens
    9 years ago

    Just playing around with CROSSJOIN and UNION as well and maybe a GENERATE using the nonexisting and ROW to provide space for the BLANK value :-) but now I have to take some sleep

  • OwenAuger's avatar
    OwenAuger
    9 years ago

    Using DAX you can do something like this, but M is probably preferable.

     

    Result =
GENERATEALL (
    'Table 1',
    VAR Table1ID = 'Table 1'[id]
    RETURN
        SELECTCOLUMNS (
            CALCULATETABLE ( 'Table 2', 'Table 2'[id] = Table1ID ),
            "price", 'Table 2'[price]
        )
)
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    mim

     

    Something like this should work. I tested it in a dummy model with physical tables Transformed_TAR, Tostr and 'Table 2' and it worked for me.

     

    Note that I've left you VAR Table1... unchanged except that I've been pedantic and qualified all your column names from Transformed_TAR and Tostr with their table names, e.g. [tag] becomes Transformed_Tar[tag] :

     

    Result =
VAR Table1 =
    UNION (
        SELECTCOLUMNS (
            FILTER (
                Transformed_TAR,
                Transformed_TAR[current] = "yes"
                    && Transformed_TAR[rem_qty] <> 0
                    && Transformed_TAR[project phase] = "cons"
                    && Transformed_TAR[P6 ACTIVITY ID] <> BLANK ()
            ),
            "tag", Transformed_TAR[tag],
            "id", Transformed_TAR[P6 ACTIVITY ID],
            "subscan", Transformed_TAR[subscan],
            "subsystem", Transformed_TAR[TOSTR_Subsystem],
            "area", Transformed_TAR[Transformed Area],
            "weight", Transformed_TAR[weight],
            "drawing", Transformed_TAR[drawing],
            "phase", Transformed_TAR[project phase],
            "CREW", Transformed_TAR[crew],
            "Module", Transformed_TAR[Module],
            "rem_qty", Transformed_TAR[rem_qty]
        ),
        SELECTCOLUMNS (
            FILTER ( Tostr, Tostr[remaining_ITR] <> 0 && Tostr[P6 activity id] <> BLANK () ),
            "tag", Tostr[tag],
            "id", Tostr[P6 activity id],
            "subscan", Tostr[subscan],
            "subsystem", Tostr[SUBSYSTEM],
            "area", Tostr[Area],
            "weight", Tostr[weight],
            "drawing", Tostr[SHEET],
            "phase", Tostr[Phase],
            "CREW", Tostr[crew],
            "Module", Tostr[Module],
            "rem_qty", Tostr[remaining_ITR]
        )
    )
RETURN
    GENERATEALL (
        Table1,
        VAR Table1ID = [id]
        RETURN
            SELECTCOLUMNS (
                CALCULATETABLE ( 'Table 2', 'Table 2'[id] = Table1ID ),
                "price", 'Table 2'[price]
            )
    )
OwenAuger's avatar
OwenAuger
Super User
9 years ago

mim

 

Something like this should work. I tested it in a dummy model with physical tables Transformed_TAR, Tostr and 'Table 2' and it worked for me.

 

Note that I've left you VAR Table1... unchanged except that I've been pedantic and qualified all your column names from Transformed_TAR and Tostr with their table names, e.g. [tag] becomes Transformed_Tar[tag] :

 

Result =
VAR Table1 =
    UNION (
        SELECTCOLUMNS (
            FILTER (
                Transformed_TAR,
                Transformed_TAR[current] = "yes"
                    && Transformed_TAR[rem_qty] <> 0
                    && Transformed_TAR[project phase] = "cons"
                    && Transformed_TAR[P6 ACTIVITY ID] <> BLANK ()
            ),
            "tag", Transformed_TAR[tag],
            "id", Transformed_TAR[P6 ACTIVITY ID],
            "subscan", Transformed_TAR[subscan],
            "subsystem", Transformed_TAR[TOSTR_Subsystem],
            "area", Transformed_TAR[Transformed Area],
            "weight", Transformed_TAR[weight],
            "drawing", Transformed_TAR[drawing],
            "phase", Transformed_TAR[project phase],
            "CREW", Transformed_TAR[crew],
            "Module", Transformed_TAR[Module],
            "rem_qty", Transformed_TAR[rem_qty]
        ),
        SELECTCOLUMNS (
            FILTER ( Tostr, Tostr[remaining_ITR] <> 0 && Tostr[P6 activity id] <> BLANK () ),
            "tag", Tostr[tag],
            "id", Tostr[P6 activity id],
            "subscan", Tostr[subscan],
            "subsystem", Tostr[SUBSYSTEM],
            "area", Tostr[Area],
            "weight", Tostr[weight],
            "drawing", Tostr[SHEET],
            "phase", Tostr[Phase],
            "CREW", Tostr[crew],
            "Module", Tostr[Module],
            "rem_qty", Tostr[remaining_ITR]
        )
    )
RETURN
    GENERATEALL (
        Table1,
        VAR Table1ID = [id]
        RETURN
            SELECTCOLUMNS (
                CALCULATETABLE ( 'Table 2', 'Table 2'[id] = Table1ID ),
                "price", 'Table 2'[price]
            )
    )
Muki's avatar
Muki
Helper I
8 years ago

Hi OwenAuger

 

I have a similar issue for which I'm looking for a solution. It's actually a merge query but as I'm not allowed to use the 'merge' functionality from power query (scheduled refresh on power bi server does not support that) I have to use DAX syntax to mimic this.

 

Here is the situation:

 

- merge Table A, Table B and Table C into new table_merged

- I need all records from Table A

- Add matching records from Table B as new columns where tableA.version= tableB.version and tableA.version2=tableB.version2

- if there is no matching record from Table B:

  • first match Table C where tableA.version= tableC.version and tableA.version2=tableC.version2
  • match Table B where tableB.version= tableC.version3 and tableA.version2=tableC.version4

 

Hope the picture below makes it more clear :)

 

thanks in advance!

Muki

 

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    Hi Muki

    It would certainly be much easier to do these merges in Power Query! :)

    But if you have to do this in DAX, here is some sample code (which I'm sure can be improved upon by the way!)

    I've assumed the tables are named TableA, TableB & TableC.

     

    The last DISTINCT step may not be necessary...not sure. It doesn't matter for your sample data, but it could matter in cases where Table C is not required but merging with it in step MERGE_ABCB results in some un-needed duplicate rows.

     

    MergedTable =
VAR Merge_AB =
    GENERATEALL (
        TableA,
        FILTER (
            TableB,
            TableB[tableB.version] = TableA[tableA.version]
                && TableB[tableB.version2] = TableA[tableA.version2]
        )
    )
VAR MERGE_CB =
    GENERATEALL (
        TableC,
        FILTER (
            TableB,
            TableB[tableB.version] = TableC[tableC.version3]
                && TableB[tableB.version2] = TableC[tableC.version4]
        )
    )
VAR MERGE_ABCB =
    GENERATEALL (
        Merge_AB,
        SELECTCOLUMNS (
            FILTER (
                MERGE_CB,
                TableC[tableC.version] = TableA[tableA.version]
                    && TableC[tableC.version2] = TableA[tableA.version2]
            ),
            "tableC.ID", TableC[tableC.ID],
            "tableC.startdate", tableC[tableC.startdate],
            "tableC.enddate", tableC[tableC.enddate],
            "tableC.version", tableC[tableC.version],
            "tableC.version2", tableC[tableC.version2],
            "tableC.version3", tableC[tableC.version3],
            "tableC.version4", tableC[tableC.version4],
            "tableB.ID_copy", tableB[tableB.ID],
            "tableB.startdate_copy", tableB[tableB.startdate],
            "tableB.enddate_copy", tableB[tableB.enddate],
            "tableB.version_copy", tableB[tableB.version],
            "tableB.version2_copy", tableB[tableB.version2]
        )
    )
VAR MERGE_CLEAN =
    SELECTCOLUMNS (
        MERGE_ABCB,
        "tableA.ID", tableA[tableA.ID],
        "tableA.startdate", tableA[tableA.startdate],
        "tableA.enddate", tableA[tableA.enddate],
        "tableA.version", tableA[tableA.version],
        "tableA.version2", tableA[tableA.version2],
        "tableB.ID", IF ( ISBLANK ( tableB[tableB.ID] ), [tableB.ID_copy], tableB[tableB.ID] ),
        "tableB.startdate", IF (
            ISBLANK ( tableB[tableB.startdate] ),
            [tableB.startdate_copy],
            tableB[tableB.startdate]
        ),
        "tableB.enddate", IF (
            ISBLANK ( tableB[tableB.enddate] ),
            [tableB.enddate_copy],
            tableB[tableB.enddate]
        ),
        "tableB.version", IF (
            ISBLANK ( tableB[tableB.version] ),
            [tableB.version_copy],
            tableB[tableB.version]
        ),
        "tableB.version2", IF (
            ISBLANK ( tableB[tableB.version2] ),
            [tableB.version2_copy],
            tableB[tableB.version2]
        ),
        "tableC.ID", [tableC.ID],
        "tableC.startdate", [tableC.startdate],
        "tableC.enddate", [tableC.enddate],
        "tableC.version", [tableC.version],
        "tableC.version2", [tableC.version2],
        "tableC.version3", [tableC.version3],
        "tableC.version4", [tableC.version4]
    )
VAR MERGE_CLEAN_DISTINCT =
    DISTINCT ( MERGE_CLEAN )
RETURN
    MERGE_CLEAN_DISTINCT