Forum Discussion
Left outer join creating many to many relation - DB2 database
Thank you v-kpoloju-msft for your response. I was able to solve the issue by creating a new table using DAX for emergency contact. I Unioned Employee and Emergency contact based on SSN and for the missing SSN from emergency contact, I am showing blank for all the other columns. This resolved the left outer join issue and I didnt need to merge the tables. For the other table joins, I used a regular Employee table one to many join. Here is the DAX.
EMERGENCY_CONTACT =UNION (SELECTCOLUMNS ('EMERGENCY CONTACT',"SSN", 'EMERGENCY CONTACT'[Ssn],"Contact Name First",'EMERGENCY CONTACT'[Contact Name First],"Contact Name Last",'EMERGENCY CONTACT'[Contact Name Last],"Contact Name Middle",'EMERGENCY CONTACT'[Contact Name Middle],"Contact Email",'EMERGENCY CONTACT'[Contact Email],"Contact Phone Cell",'EMERGENCY CONTACT'[Contact Phone Cell],"Contact Phone Home",'EMERGENCY CONTACT'[Contact Phone Home],"Contact Phone Work",'EMERGENCY CONTACT'[Contact Phone Work],"Contact Phone Work Ext",'EMERGENCY CONTACT'[Contact Phone Work Ext],"Contact Relationship",'EMERGENCY CONTACT'[Contact Relationship],"Emergency Contact Sequence",'EMERGENCY CONTACT'[Emergency Contact Sequence],"Primary Flag",'EMERGENCY CONTACT'[Primary Flag]),SELECTCOLUMNS (EXCEPT (SELECTCOLUMNS('EMPLOYEE DETAIL', "SSN",'EMPLOYEE DETAIL'[Social Security Number]),SELECTCOLUMNS('EMERGENCY CONTACT', "SSN",'EMERGENCY CONTACT'[Ssn])),"SSN", [SSN],"Contact Name First", BLANK(),"Contact Name Last", BLANK(),"Contact Name Middle",BLANK(),"Contact Email", BLANK(),"Contact Phone Cell", BLANK(),"Contact Phone Home", BLANK(),"Contact Phone Work",BLANK(),"Contact Phone Work Ext",BLANK(),"Contact Relationship",BLANK(),"Emergency Contact Sequence",BLANK(),"Primary Flag",BLANK()))
Hi Rajani1085,
Thanks for reaching out to the community I understand how data modelling issues like this can impact your report outcomes.
From your explanation, it seems that merging the Employee table with the Employee Emergency Contact table using a left outer join is causing duplication of employee records, particularly when an employee has multiple emergency contacts. This issue hinders the establishment of a proper one-to-many relationship with the CareerProgression table, resulting in a many-to-many scenario and unexpected report behaviour.
To address this, I suggest avoiding the merge in Power Query and instead modeling the data using separate tables with relationships defined in the data model view.
Here’s what you can do:
- Remove the merged table and keep Employee, Employee Emergency Contact, and CareerProgression as individual tables.
- In the Power BI model view, create relationships as follows:
- Employee[EmployeeID] → EmergencyContact[EmployeeID] (One-to-Many)
- Employee[EmployeeID] → CareerProgression[EmployeeID] (One-to-Many)
- Utilize visuals that leverage these relationships directly. For instance, if you want to display emergency contacts and career progression together, you can use slicers or drill-through pages based on EmployeeID rather than attempting to merge them.
This approach ensures your model follows a star schema structure, maintains referential integrity, and avoids complications arising from row duplication.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
Thank you v-kpoloju-msft for your response. I was able to solve the issue by creating a new table using DAX for emergency contact. I Unioned Employee and Emergency contact based on SSN and for the missing SSN from emergency contact, I am showing blank for all the other columns. This resolved the left outer join issue and I didnt need to merge the tables. For the other table joins, I used a regular Employee table one to many join. Here is the DAX.
Hi Rajani1085,
Thank you for sharing your resolution. Excellent work utilizing DAX to manage the left outer join scenario with UNION and EXCEPT. Your method effectively ensures all employees are included in the resulting table, even if they lack an emergency contact, by explicitly adding blank rows for missing SSNs from the EMERGENCY CONTACT table. This approach is a strong alternative to merging tables in Power Query and is effective in DAX-driven models.Additionally, it's good to know that your one-to-many relationship with the main EMPLOYEE DETAIL table is working as intended.
Also, if you found this approach helpful and it resolved your issue, feel free to mark you’re post as the accepted solution. This helps others in the community easily find working solutions for similar scenarios.
Thank you for using Microsoft Community Forum.Hi Rajani1085,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.