Data Modeling multiple value bridge tables

[jswartz_09]

Hi everyone, our company is working towards implementing PowerBI as our sole BI tool. As for this implementation I'm trying to learn as much as I can about the data modeling of PowerBI. 

In the past we simply just wrote SQL queries in order to get our information. However in modeling in PowerBI seems to be somewhat different than straight SQL. 

In our database Employees are joined to our Work Orders table. One employee may have multiple Work Orders. Off of this Work Orders table is many other tables that it can join to. 
There is also an Employment table in our database that has many that it can join to as well. 

However there often times our requestors want to see information of the Employee from both the Employment table and Work Orders table at the same time. These two tables are joined via a bridge table (Work Employment Bridge). 

This Bridge table contains both Employment IDs and Work Order IDs. Employment IDs may have many Work Order IDs, however Work Order IDs may belong to many Employment IDs. 

Generally speaking I could write my SQL as follows and get the results I want: 
Select *

from Employee e1

left join Work Orders wo1

on e1.EmployeeID = wo1.EmployeeID

left join Work Employment Bridge web

on wo1.WorkOrderID = web.WorkOrderID

left join Employment e2

on web.EmploymentID = e2.EmploymentID

left join Work Completion wc

on wo1.WorkOrderID = wc.WorkOrderID

Below is what my modeling looks like in PowerBI

It seems that when I put information from Employee table, Work Order Table and Employment table in a visual everything works fine. I believe this is because they all follow a one to many relationship. 

However when I bring in information from the Work Completion table it results in an error, because the Employment information is also in the visual. 

Now I know I could join from the Work Employment Bridge table to the Work Completion table, but that creates a many to many relationship. In addition there is many other tables like Work Completion that join from Work Orders in a 1 to many relationship as well, same goes for Employment table. I hate to do dozens of many to many relationships. 

Any advice on how I can get this all to work similarly to my SQL statement?

Employment Sample  

[v-kpoloju-msft]

Hi @jswartz_09,

Thanks for sharing the detailed background and model view that really helps. Also, thanks to @DataNinja777, @Kedar_Pande, for those inputs on this thread.

The issue you are running into happens because the Employment and Work Orders tables are connected through a many-to-many bridge. When you add Work Completion data to visuals that also include Employment fields, Power BI can’t find a single filter path, which causes ambiguity. This differs from SQL joins, where the engine handles row-by-row relationships automatically.

To fix this, start by simplifying your bridge table so it only includes unique pairs of EmploymentID and WorkOrderID, and set the filter direction to flow one way (Employment → Bridge → Work Orders → Work Completion). If you still need to combine Employment and Completion data in the same visual, you can use a DAX measure with the TREATAS function to virtually link them without creating more many-to-many relationships.

Refer these links:
1. https://learn.microsoft.com/en-in/dax/treatas-function-dax 
2. https://learn.microsoft.com/en-in/power-bi/transform-model/desktop-many-to-many-relationships 

Hope this helps you get your model working the way you expect.

Thank you for using the Microsoft Fabric Community Forum.

[jswartz_09]

Yeah sorry someone already answered like this. Unfortunately I have to include a column from each one of the tables into a single table visual. Creating a measure with TRETAS is not what I want to do. 
so how would I include a column from each table in a visual?

[v-kpoloju-msft · Accepted solution]

Hi @jswartz_09,

Thank you for clarifying. When you try to include columns from Employment, Work Orders, and Work Completion in one visual, Power BI can’t determine a single filter path because these tables are linked through a many-to-many bridge. Unlike SQL joins, Power BI visuals rely on relationships and filter propagation, so multiple many-to-many paths cause ambiguity that prevents all columns from displaying correctly.

If you need to show columns from each table side by side, the best approach is to flatten the data in Power Query by merging the tables (like your SQL join) or create a summarized reporting table that combines only the needed fields. These options give you a single, unambiguous table for visuals. If you prefer to keep your current model structure, measures like TREATAS can still work for calculations, but visuals showing columns from all tables will require a combined dataset.

Refer these links:
1. https://learn.microsoft.com/en-us/power-query/merge-queries-overview 
2. https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616... 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[v-kpoloju-msft]

Hi @jswartz_09,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @jswartz_09,

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[v-kpoloju-msft]

Hi @jswartz_09,
Thank you for reaching out to the Microsoft Fabric Community Forum. 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Thank you.

[jswartz_09]

Since I'm not a super user I can't attach a file. How am I supposed to provide sample data?

[v-kpoloju-msft]

Hi @jswartz_09,

Kindly upload the sample data to OneDrive so that we can efficiently reproduce the scenario and assist you accordingly.

Thank you.

[jswartz_09]

Ok sample data uploaded. Hopefully it works

[DataNinja777]

Hi @jswartz_09 ,

The type of requirement I saw in the past usually involve either staff booking system in consulting firms using SAP or timesheet where staff are required to enter Work Order ID and account for hours worked on each Work Order ID for given period.  In such scenario, the raw data has Employee ID, Work Order ID, Date and number of hours charged, and this is the fact table which can be linked to Work Order table and Employee table. Since the data of the employee worked or booked on each project is a fact table (transaction table), this set up allows it to keep clean one to many relationship between the dimention tables (Work Order ID, Employee ID, Date table), and fact table (of timesheet record or booking record).  

Would you be able to tell me what sort or fact table you keep in your system?  

Best regards,

[jswartz_09]

So the struggle is we don't necessarily have a big FCT table. We kind of have like 3 different Domains so to speak. A bunch of tables join off the Employee table. A bunch of tables join off the Work Orders table, and a bunch of tables join off the Employment table. 
The bridge between Employment and Work Orders is that Work Order Employment Bridge

[Kedar_Pande]

1. Remove the relationship between 'Work Employment Bridge' and 'Employment'.

2. Create two inactive relationships:

   • From 'Work Employment Bridge'[WorkOrderID] to 'Work Orders'[WorkOrderID]

   • From 'Work Employment Bridge'[EmploymentID] to 'Employment'[EmploymentID]

   • For any measure needing data from both 'Work Orders' and 'Employment', use USERELATIONSHIP to activate the correct path.

@jswartz_09

[jswartz_09]

So this is fine for measures, but what if I want to have Employee Table, Work Orders Table, Employment Table, and Work Completion table columns all in the same Table?