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] ) )
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 ?
GENERATEALL should work even if Table 1 is not physical.
The expression we had didn't rely on the lineage of any columns in Table 1, so it should be possible to use it with Table 1 as a variable.
The only difference is when referring to a column of Table 1 (as a variable) in the row context of Table 1, you don't need to qualify the column name with a table name. So rather than 'Table 1'[id] you would just use [id].
Can you post the new expression you are trying to write, with detail on any phyiscal tables that Table 1 depends on?
Hi OwenAuger ,
Can Table2 be Variable like Table 1. I have defined Table 1 and table 2 are defined in the same DAX window but Generateall is not working but it is working If one of the table is a physical table.
Could you please provide a sample with being Table 1 an dTable are declared as variables.
OwenAuger thanks , Table 1 is is based on this expression
var Table1= union( SELECTCOLUMNS(filter(Transformed_TAR,[current]="yes"&&[rem_qty]<>0&&[project phase]="cons"&&[P6 ACTIVITY ID]<>BLANK()),"tag",[tag],"id",[P6 ACTIVITY ID],"subscan",[subscan],"subsystem",[TOSTR_Subsystem],"area",[Transformed Area],"weight",[weight],"drawing",[drawing],"phase",[project phase],"CREW",[crew],"Module",[Module],"rem_qty",[rem_qty]),SELECTCOLUMNS(filter(Tostr,[remaining_ITR]<>0&&[P6 activity id]<>BLANK()),"tag",[tag],"id",[P6 activity id],"subscan",[subscan],"subsystem",[SUBSYSTEM],"area",[Area],"weight",[weight],"drawing",[SHEET],"phase",[Phase],"CREW",[crew],"Module",[Module],"rem_qty",[remaining_ITR]))
the id = "id"
cheers
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] ) )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
