Issue with connecting multiple tables

Hi bondrood 

Welcome to the Microsoft Fabric Community Forum. Thank you mark_endicott and MasonMA for sharing helpful insights.

To address the issue of connecting multiple tables without direct relationships, it is recommended to use Power Query for data transformation before loading your model. Here, you have three tables: Table 1 contains company details, Table 2 has company type definitions, and Table 3 links users to both companies and company types. Since Table 1 doesn't directly reference company types, Table 3 can serve as a bridge.

First, merge Table 3 with Table 2 using the CompanyTypeId field to bring company type information into Table 3. Next, group Table 3 by CompanyId and select a representative company type for each company, such as the first non-null value or based on custom logic if there are multiple types. This produces a summary table with one company type per company. Finally, merge this summary table with Table 1 using CompanyId to add the company type directly to each company record.This approach keeps your data model clear, avoids complicated DAX or ambiguous relationships, and makes ongoing maintenance simpler.

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.

Thank you for being part of Fabric Community Forum.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

Hi bondrood 

Thanks for confirming the table merge worked. Since multiple company types are linked to a single CompanyId, you can now define one representative type per company using Power Query. Group the merged table by CompanyId and apply logic like selecting the most frequent type, the most recent one , or the first non-null entry. Once identified, merge this result into Table 1 to complete the process. This keeps your reporting consistent and avoids ambiguity.


Regards,
Karpurapu D.