Forum Discussion

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
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

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

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

Super User

9 years ago

mim

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?

mim

Advocate V

9 years ago

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

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.

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

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

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

Anonymous
Not applicable
7 years ago
OwenAuger i have similar needs and Table 2 is virtual table too, need to calculate as VAR Table2 = ..... But it's not working in GENERATEALL. Any advise?
Thanks
- OwenAuger
  Super User
  7 years ago
  Anonymous
  Could you post more detail on your particular tables & expected result?

Forum Discussion

left outer join using dax, Multiple to Multiple

Related content

MULTIPLE CARD

Multiple dataViewMappings

Merge data from multiple excel files from multiple sharepoint folders

Disable multiple eventstreams

IF, Multiple