Left outer join with many to many relationship

[capko]

I'm facing a problem with Power BI and a NATURALLEFTOUTERJOIN. I have two tables that I would like to join using a left outer join but I have a many to many relationship and the documentation says it's not possible unless a many to one relationship exists.

I cannot use Power Query since the first table is a calculated one (not visible in Power Query). I created a simplified example that I'm trying to solve in order to apply the solution to the real model.

The first table (1) look like this:

The second one (2) like this:

And the result one will be like this:

When I stablish a many to many between (1) and (2) and I try to create a new table using NATURALLEFTOUTERJOIN(Table_1;Table2) I get the following error:

Which is logical due to the many to many relationship.

There is really no way to do a NATURALLEFTOUTERJOIN if the relation is many to many ? Any other way to do this operation in DAX ?

[Anthony_G1]

FYI to future users who are reviewing this forum post, A bridge table in DAX worked!

TableOne - User_ID

TableTwo - User_ID

User_ID --> User_ID is a Many-to-Many relationship

So I created a DAX bridge table:

User_IDBridge =
DISTINCT(
UNION(
SELECTCOLUMNS(TableOne, "User_ID", TableOne[User_ID]),
SELECTCOLUMNS(TableTwo, "User_ID", TableTwo[User_ID]),
)
)

Then I created a one-to-many relationship from this bridge table to both TableOne and TableTwo, with "Cross-filter direction = Both" turned on for both relationships.

Lastly, I was now successfully able to create a join table:

Main_Reporting_Table = NATURALLEFTOUTERJOIN(TableOne, TableTwo)

[amitchandak]

@capko , You can merge table in power queries

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo

In Dax, you can create many to many join and use the required columns and measures in visual

You can create a Bridge table- https://www.seerinteractive.com/blog/join-many-many-power-bi/

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...