Issue with connecting multiple tables

[bondrood]

Hello,

I've been having difficulty properly combining some tables. To give the most basic information about this project, I have 3 tables I'm looking to reference for a new value.

Table 1 - A table with company information (Name, CompanyId, etc.)

Table 2 - A dimensional table with CompanyTypeId relating to the name of a company type; Table 1 does NOT contain a field for the CompanyTypeId value

Table 3 - A table with User information; this table lists company type connected to Users

Table 1 and Table 3 have a 1:* connection. Table 2 and Table 3 have a *:1 connection. I'm looking to establish a new column in Table 1 that references the Id value in Table 2 while the connection is established through Table 3. When I attempt to do it now, I receive either an error about unexpected table with multiple values or a doesn't exist/doesn't have a relationship error. Let me know if there's anything else I can provide and I greatly appreciate any assistance!

Let me know if there's anything else I can provide and I appreciate any assistance!

[v-karpurapud · Accepted solution]

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.

[bondrood]

I appreciate all your responses! I've merged the tables but I've discovered there are many different types associated with each Company ID. I'm not sure how much I can do about this but the advice to merge is much appreciated

[v-karpurapud · Accepted solution]

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.

[v-karpurapud · Accepted solution]

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.

[MasonMA]

Hi, also if your model allows for transfomation in Power Query, i'd suggest you merge tables there to avoid dealing with relationships. Based on your logic, you may try Merging 'Table 3' with 'Table 2' on CompanyType, then group by CompanyID and choose the first CompanyTypeName for aggregation. with the grouped result you can merge it again with 'Table 1'. 

[mark_endicott]

@bondrood - it sounds like what you are trying to do is going to introduce ambiguity to your model, something that will give you false answers as Power BI will not know which routeway to take when performing calculations. 

Fortunately you can get around this, and still acheive the correct results through implementing virtual relationships in measures, here's a couple of great articles to set you on your way: 

https://www.sqlbi.com/articles/physical-and-virtual-relationships-in-dax/ 

https://www.sqlbi.com/articles/relationships-in-power-bi-and-tabular-models/#:~:text=discussing%20pe....

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!