Forum Discussion
Data Modeling multiple value bridge tables
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-7ca2616d17f9Hope 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.
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,
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
