Solving Ambiguous Pathway Error

[RileyPilcher]

Hi,

I am an amateur Power BI user trying to create a project managament dashboard.  I am running into an issue where i need to have a relationship between two tables, but when i make the join it gets flagged as creating an ambiguous pathway.  This is because these two tables are both connected to another table.  Further details below.

Three tables:

- Project table (includes all projects: project number, description, value, etc.)

- Timesheets (includes all timesheets: employee, project number, work instruction number, hours, cost, etc.)

- Work Instructions (includes all work instructions: WI number, project number, description, allowed timeframe, status [active/complete] etc.)

Current joins:

- Project > Timesheets (one to many)

- Project > Work Instructions (one to many)

The above joins have allowed me to generate multiple visuals on my dashboard.  However, i need to relate the Work Instruction number column of the Work Instructions and Timesheets tables, as below, but i get an ambiguous path error.

Wanted/needed join:

- Work Instructions > Timesheets (one to many)

The reason i want/need this join is I am trying to apply a filter to a donut chart to only show the hours remaining on active work instructions. I have calculated remainaing hours as (estimated hours of work instruction - total hours worked on work instruction).  I have this filter set but it only applys to the Work Instruction table data but not to the Timesheets data as theres no relationship there to allow this.

I would be very happy to hear any advice or reccomendations.  Thanks.

Riley

[Anonymous · Accepted solution]

Hi @RileyPilcher ，

You can also try to create many-to-many relationship, but only allow Work Instructions to filter Timesheets.

Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn

Best regards,

Mengmeng Li

[RileyPilcher]

Thank you very much.  This solved my problem instantly.

[Kedar_Pande]

@RileyPilcher 

Create a bridge table that contains unique values of Work Instruction Number and link it to both the Work Instructions and Timesheets tables.

Create a new table:

WorkInstructionBridge = DISTINCT('Work Instructions'[WI Number])

Create relationships:
Work Instructions[WI Number] → WorkInstructionBridge[WI Number] (One-to-Many)
Timesheets[WI Number] → WorkInstructionBridge[WI Number] (Many-to-One)
Use the WorkInstructionBridge table in your measures and slicers to avoid ambiguous paths.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn