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

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

OwenAuger
Super User
Super User

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

Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

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

Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

24 REPLIES 24

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.

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.



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors