Forum Discussion
left outer join using dax, Multiple to Multiple
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
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] ) )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] ) )
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 intially I used crossjoin with filter, and it was a little slow, around 1 minutes, than i tried your approach !!!!! less than a second.
when you have time, do you mind explaining your appraoch.
thanks again.
Hi mim
Quoting from The Definitive Guide to DAX:
"you might consider the GENERATE function similar to the CROSS APPLY condition in SQL, whereas GENERATEALL is similar to OUTER APPLY in SQL".
GENERATE/GENERATEALL iterate over the rows of first table specified and determine the second table in the context of each row, with freedom as to how the condition relates to that row context.
Performance-wise, I would imagine they perform better than a FILTER(CROSSJOIN()) pattern when the join results in a small subset of the rows of the CROSSJOIN of the two tables.
I haven't really played with NATURALLEFTOUTERJOIN or NATURALINNERJOIN, but I note they require join columns to have the same lineage.
Owen :)
OwenAuger thanks for the explanation, i have been using your solution for the last two weeks and it is working perfectly.
currently table 1 is a calculated table, I tried instead of making Table 1 as a variable using the same expression to built Table 1, but could not make it to works, do you think it is possible or generateall required Table 1 to be physical ?
Thx OwenAuger, that helped me a lot !
