cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mim Member
Member

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 read about the new " naturalleftoutjoin ", i can't make it to works, if I add the common id, it complains that there is a duplicate columns, if i remove it, it say there is no common column, clearly i don't understand how it works.

so here is my scenario; notice the id is duplicated in both tables and it is by design, so any idea how to create the calculated table Result using DAX ?

 

left join outer.PNG

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Highlighted
OwenAuger Super Contributor
Super Contributor

Re: left outer join using dax, Multiple to Multiple

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

OwenAuger Super Contributor
Super Contributor

Re: left outer join using dax, Multiple to Multiple

@mim

 

Something like this should work. I tested it in a dummy model with physical tables Transformed_TARTostr 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]
            )
    )


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

23 REPLIES 23
Super User
Super User

Re: left outer join using dax, Multiple to Multiple

Hey,

 

this is odd, I created the screenshot from this

 

little example

 

recreating your sample data, a table merge using left outer creates this result

LeftOuter.png

 

Really wondering whats going on

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
mim Member
Member

Re: left outer join using dax, Multiple to Multiple

I need to do it in DAX using calculated table, not PowerQuery, the reason is table 1 and table 2 are already loaded in the Data model using some heavy PowerQery Transformation, which take nearly 20 minutes, 

 

ideally if PQ supported loading table from existing data model, it would have been great, but that's not the case now, I thought it can be possible using DAX.

Super User
Super User

Re: left outer join using dax, Multiple to Multiple

Silly me, sorry for bothering ...

 

The DAX functions NATURALLEFTOUTERJOIN() requires an relationship between the tables (one to many), seems a little strange, but thats how the function currently works.

 

And you can't create a relationship in dax because none of the two columns is unique.

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
mim Member
Member

Re: left outer join using dax, Multiple to Multiple

@TomMartens thanks Tom, I thought maybe crossjoin with filter on a common id, plus union where the id do not match may do the tricks !!! 

Super User
Super User

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Highlighted
OwenAuger Super Contributor
Super Contributor

Re: left outer join using dax, Multiple to Multiple

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

mim Member
Member

Re: left outer join using dax, Multiple to Multiple

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

OwenAuger Super Contributor
Super Contributor

Re: left outer join using dax, Multiple to Multiple

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 🙂



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mim Member
Member

Re: left outer join using dax, Multiple to Multiple

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 312 members 2,875 guests
Please welcome our newest community members: