Forum Discussion
Helper IError: Join paths are expected to form a tree when creating a Unique Technology table
In a report using a live connection, I want to add a new table that contains unique Technology values using the measure below. After creating this new table and trying to join it with the Products and Details tables, I ended up with a strange relationship. When I add the Technology field from the newly created table to the report, I get an error saying: Join paths are expected to form a tree...
Could someone advise what the issue might be? Thanks
Unique Technology =
VAR Products =
SELECTCOLUMNS(
'Products',
"Technology", 'Products'[Technology]
)
VAR Details =
SELECTCOLUMNS(
'Details',
"Technology", 'Details'[Technology]
)
RETURN
DISTINCT( UNION(Products, Details) )
Hi Julia2023
The error "Join paths are expected to form a tree" typically occurs in Composite Models (DirectQuery over Power BI datasets) when a calculated table attempts to create a physical relationship back to the source tables from which it was derived.
In your case, since the Unique Technology table is created using UNION and DISTINCT from the Products and Details tables, the engine cannot establish a physical relationship between them. Doing so creates a circular dependency in the model's metadata—the calculated table depends on the remote tables for its data, but the remote tables would then depend on the calculated table for filtering.
Recommended Solutions
1. Move the Logic to the Source Model (Best Practice)
The most robust solution is to create the Unique Technology dimension table directly in the source "Golden Dataset." Once it is part of the source model, you can connect to it via the Live Connection/DirectQuery link as a standard table, and the relationships will work without issues.2. Use Virtual Relationships (TREATAS)
If you cannot modify the source model, avoid creating physical relationships in the diagram. Instead, use the TREATAS function in your measures to apply filters virtually.Example:
קטע קוד
Total Sales =
CALCULATE(
SUM('Details'[Sales Amount]),
TREATAS(VALUES('Unique Technology'[Technology]), 'Details'[Technology])
)
3. Check for Relationship Ambiguity
If you are working with multiple source groups, ensure that your relationships do not create multiple paths between two tables. In Composite Models, every table must have a single, unambiguous path to any other table in the "tree" structure. If your new table creates a second path between Products and Details, the engine will trigger this error.Summary: You are hitting a metadata limitation of Composite Models. To fix it, either move the dimension table to the source or switch to virtual filtering using DAX.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
2 Replies
- FBergamaschi
Super User
Hello Julia2023,
so yo ucreated a composite model. when you add tables, you add them to the local model created and so relationships are weak (different data islands). Nothing fancy here.
That said, the message you are getting seems to imply there are ambigous paths due to the new relationship you created.
In otder to help, I need to have a clear picture of the model as it was before your new table addition and as it is after your new table was addes, can you please show two pictures of the entire model in the two above mentioned conditions?
Best
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
- Ritaf1983
Hi Julia2023
The error "Join paths are expected to form a tree" typically occurs in Composite Models (DirectQuery over Power BI datasets) when a calculated table attempts to create a physical relationship back to the source tables from which it was derived.
In your case, since the Unique Technology table is created using UNION and DISTINCT from the Products and Details tables, the engine cannot establish a physical relationship between them. Doing so creates a circular dependency in the model's metadata—the calculated table depends on the remote tables for its data, but the remote tables would then depend on the calculated table for filtering.
Recommended Solutions
1. Move the Logic to the Source Model (Best Practice)
The most robust solution is to create the Unique Technology dimension table directly in the source "Golden Dataset." Once it is part of the source model, you can connect to it via the Live Connection/DirectQuery link as a standard table, and the relationships will work without issues.2. Use Virtual Relationships (TREATAS)
If you cannot modify the source model, avoid creating physical relationships in the diagram. Instead, use the TREATAS function in your measures to apply filters virtually.Example:
קטע קוד
Total Sales =
CALCULATE(
SUM('Details'[Sales Amount]),
TREATAS(VALUES('Unique Technology'[Technology]), 'Details'[Technology])
)
3. Check for Relationship Ambiguity
If you are working with multiple source groups, ensure that your relationships do not create multiple paths between two tables. In Composite Models, every table must have a single, unambiguous path to any other table in the "tree" structure. If your new table creates a second path between Products and Details, the engine will trigger this error.Summary: You are hitting a metadata limitation of Composite Models. To fix it, either move the dimension table to the source or switch to virtual filtering using DAX.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
