Forum Discussion
Helper IILeft outer join with many to many relationship
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 ?
2 Replies
- amitchandak
Super User
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-solution-8e3eccb41bda 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)
