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]
            )
    )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

I'm looking for solution to left out join 2 virtual tables according to filter context and thus find this thread with your excellent DAX. Instead of giving all new data, we can reuse Mim's case, can you replace Table 2 to virtual table? e.g. VAR table_2 = SELECTCOLUMNS('Table 2', "id", 'Table 2'[id], "price", 'Table 2'[price]). Then use it in GenerateAll. 

 

BTW, as you mentioned, i tried NATURALLEFTOUTERJOIN() but got data type/data linage issue. 

Thanks.

OwenAuger's avatar
OwenAuger
Super User
7 years ago

Anonymous

If I understand you correctly, you should be able to do something like this, using FILTER instead of CALCULATETABLE.

Result =
VAR table_2 =
    SELECTCOLUMNS ( 'Table 2', "id", 'Table 2'[id], "price", 'Table 2'[price] )
RETURN
    GENERATEALL (
        'Table 1',
        VAR Table1ID = 'Table 1'[id]
        RETURN
            SELECTCOLUMNS ( FILTER ( table_2, [id] = Table1ID ), "price", [price] )
    )

I have assumed we start with the same physical tables as Mim's example and make table_2 a virtual table as you've described. Table 1 could also be a virtual table and it would make little difference.