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()))
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.