Left outer join creating many to many relation - DB2 database

[Rajani1085]

Hello Experts,

I am stuck with a data model issue in Power BI. I have 2 tables (employee and employee emergency contact) joined in left outer join relation with one - many. I created the left outer join using merge option in Power BI. Now, this new merged table (Employee/Employee emergency contract) should be joined with other tables, for example career progression on employee id. One employee id from merged table can have multiple career progression (one – many) but due to merging employee and employee and emergency contact, it is creating many to many relationships and it’s affecting the reports in power bi. Could you please suggest how do I resolve this issue and create one - many join with merged table.

[v-kpoloju-msft]

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.

[Rajani1085 · Accepted solution]

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

    )

)

[v-kpoloju-msft]

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.

[v-kpoloju-msft]

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.

[Rajani1085]

Thank you so much for your response. I am using Power BI report builder for designing the report. Also, there are scenarios where user would like to see all the employee details along with emergency contact irrespective of whether an employee has emergency contact or not. That was the reason, I created the left outer join using merge option. Please suggest, how do I handle this situation while designing the report in report builder. And management is pushing to open this project for ad-hoc reporting for business users. in Ad-hoc reporting, they may use paginated report or desktop report. Please suggest.

[v-kpoloju-msft]

Hi @Rajani1085,

Thank you for your detailed follow-up. I understand you are working with Power BI Report Builder and need to display all employees, including those without emergency contacts, while also supporting career progression data and preparing the model for ad-hoc reporting scenarios.

You are correct in using a left outer join to include all employee records. However, as you've noted, merging Employee and Emergency Contact tables in Power Query (or as a dataset in Report Builder) can lead to row duplication, which then impacts your ability to maintain a clean one-to-many relationship with tables like CareerProgression. Here’s how you can manage this effectively in Report Builder and future ad-hoc scenarios:

Use Separate Datasets Instead of Merging in Power BI or Power Query: In Report Builder, keep Employee, EmergencyContact, and CareerProgression as separate datasets. Use SQL for the Employee–EmergencyContact dataset with a LEFT JOIN:

SELECT e.EmployeeID, e.Name, ec.ContactName, ec.Relationship

FROM Employee e

LEFT JOIN EmergencyContact ec ON e.EmployeeID = ec.EmployeeID

Create another dataset for Career Progression:

SELECT EmployeeID, JobTitle, PromotionDate

FROM CareerProgression

Implement Grouping and Parameters in Report Design: Utilize EmployeeID as a key field to group or link data visually. Consider using nested tablix or subreports to present Emergency Contact and Career Progression sections for each employee, ensuring a clear data structure.

For Ad-Hoc Reporting (Power BI Desktop): Avoid merging tables in Power Query. Instead, build relationships in the model:

• Employee[EmployeeID] → EmergencyContact[EmployeeID] (1:* One-to-Many)
• Employee[EmployeeID] → CareerProgression[EmployeeID] (1:* One-to-Many)

Design visuals that rely on these relationships rather than merged flat tables. This method preserves the integrity of relationships, prevents many-to-many issues, and ensures that both Report Builder and Power BI Desktop are prepared for ad-hoc use cases, without data duplication or excessive visuals.

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.