Forum Discussion
InvalidUnconstrainedJoin — sibling tables sharing same hub
Hi community,
I'm delivering a semantic model to end users who will freely build their own visuals — I have no control over which tables or columns they combine.
My model has a central table called Projet with several child tables connected to it. Some are m:1 (Commande FTTO, Gouv, Task) and some are 1:1 (Commande BTE, Modules SFP). relationships are in SingleDirection or Bothdirection.
The problem I'm facing is that whenever a user builds a visual combining columns from two sibling tables — for example Project, Commande FTTO and Gouv together — Power BI throws an InvalidUnconstrainedJoin error. Based on my research this happens because Projet cannot act as a natural bridge table when columns from two siblings are used simultaneously in the same visual without a measure, which is well explained in the SQLBI article on this topic.
I already tried several things. Switching to BothDirections on both relationships didn't help, same error. Adding a COUNTROWS measure removes the error but creates a cartesian product between the sibling tables which gives completely wrong results — for a project that has 2 rows in Commande FTTO and 0 matching rows in Gouv, the visual returns 10 rows instead of 2. FIRSTNONBLANK with USERELATIONSHIP has the same cartesian product issue at the row level.
One thing I noticed is that combining Commande BTE and SFP Client in the same visual works perfectly fine without any error.
The measure workaround is not acceptable in my case for two reasons. First, end users are not developers and I cannot expect them to always add a measure to every visual they create. Second, even when they do add a measure, the cartesian product makes the results completely wrong anyway.
So my question is: is there a proper model-level solution that allows any combination of sibling tables in any visual without errors and without cartesian products, without requiring end users to add measures?
I already went through the SQLBI article on this error Understanding the “can’t determine relationship between the fields” error in Power BI - SQLBI) and the Microsoft Learn documentation on relationships but couldn't find a definitive model-level answer.
Here is the ERD of the current model structure:
Thanks in advance, any insight or experience on this kind of modeling challenge is really appreciated.
Hi all,
Thank you @Azadsingh and @Kiran-7312 for confirming this is a modeling limitation rather than a relationship configuration issue.
After further discussion internally, we've settled on a solution: since end users don't functionally need KPIs or a proper star schema (fact/dimension). we will not build (or allow) any report/visual that pulls data simultaneously from the three tables that have a 1:m relationship with the Projet hub table.
In practice this means:
- Visuals can combine Projet with one of the "many" child tables (Commande FTTO, Gouv, or Task) at a time, which works fine since there's a single, unambiguous filter path.
- The 1:1 tables (Commande BTE, Modules SFP) can still be freely combined with each other and with the hub, as already confirmed.
- We're not attempting a model-level fix (bridge table, flattening, etc.) since it's not required by the actual reporting needs — we're just constraining what combinations are exposed to end users, either through separate pages/visuals per child table or by governing which fields can be used together.
This avoids the InvalidUnconstrainedJoin error and the cartesian product issue entirely, without requiring end users to add measures.
Thanks again for the insight — it helped confirm that this is fundamentally a data granularity/modeling issue, not something fixable purely through relationship settings.
6 Replies
Hi,
In addition to the suggestions already shared, one thing to verify is whether the sibling tables are actually at the same level of granularity. Even if they all relate to the same Project table, combining detail-level columns from multiple fact-like tables in a single visual can lead to an `InvalidUnconstrainedJoin` because Power BI cannot determine a valid row-level relationship.
If end users need to freely combine fields from these tables without relying on measures, you may want to consider:
* Creating a denormalized reporting table if the data represents the same business process.
* Using conformed dimension tables with measures if they represent different business processes.
* Reviewing whether some descriptive attributes currently stored in fact tables could instead belong in dimension tables.Could you also clarify whether `Commande FTTO`, `Gouv`, and the other tables are transactional fact tables or lookup/detail tables? That will help determine the best modeling approach.
I don't think there's a model setting that will completely solve this.
The invalid join error is usually a symptom of the data model rather than the relationship direction. Even if you change relationships to Both, Power BI still can't determine how to pair rows between two sibling fact tables because there isn't a direct relationship between them—only a shared parent (project) That's why the error persists.
The reason your 1:1 tables work is that Power BI can uniquely identify a single related row on each side. With two child tables that each contain multiple rows per project, there isn't enough information to know which rows should be matched together.
Adding a measure only forces the engine to evaluate the visual, but it doesn't create a valid row-level relationship. If both child tables contain multiple rows for the same project, you'll naturally end up with a many-to-many expansion (cartesian product), which is exactly what you're seeing.
If your users need to freely combine columns from different child tables without worrying about measures or relationship behavior, I'd look at redesigning the semantic model rather than the relationships. Depending on the business logic, that could mean:
Flattening the required attributes into a single reporting table.
Creating a dedicated bridge table if there is a real business key that links the child tables.
Restructuring into a proper star schema where visuals are built from dimensions and measures instead of combining multiple fact-like tables.
Unfortunately, if there is no business key that defines how commando FTO rows relate to gouv rows within the same project, there isn't a model-level feature that can infer that relationship automatically. Power BI is preventing an ambiguous join rather than failing to find one.
So I'd say this is fundamentally a modeling challenge rather than a relationship configuration issue.
Helpful? Give a Kudos 👍Solved? Mark as Solution ✔️— Azad Singh Thakur | Power BI DeveloperHi,
In addition to the suggestions already shared, it's worth checking whether the model is creating an unconstrained join because the sibling tables contain multiple matching rows for the same Project key. Even when all tables are connected to the same hub table, Power BI can produce this error if the relationships don't provide a unique filter path.
You could also verify:
* Whether the relationship cardinality is configured correctly (One-to-Many vs. Many-to-Many).
* If the shared Project table contains unique values for the key column.
* Whether using measures instead of placing columns from multiple fact-like tables in the same visual would better suit the model.If possible, could you share a simple diagram of the relationships or a sample of the model? That would make it easier to identify the exact cause of the `InvalidUnconstrainedJoin` error.
Hi ImaneMeskine,
Thank you for reaching out to Microsoft Fabric Community.
Thank you Kiran-7312 and Azadsingh for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi all,
Thank you @Azadsingh and @Kiran-7312 for confirming this is a modeling limitation rather than a relationship configuration issue.
After further discussion internally, we've settled on a solution: since end users don't functionally need KPIs or a proper star schema (fact/dimension). we will not build (or allow) any report/visual that pulls data simultaneously from the three tables that have a 1:m relationship with the Projet hub table.
In practice this means:
- Visuals can combine Projet with one of the "many" child tables (Commande FTTO, Gouv, or Task) at a time, which works fine since there's a single, unambiguous filter path.
- The 1:1 tables (Commande BTE, Modules SFP) can still be freely combined with each other and with the hub, as already confirmed.
- We're not attempting a model-level fix (bridge table, flattening, etc.) since it's not required by the actual reporting needs — we're just constraining what combinations are exposed to end users, either through separate pages/visuals per child table or by governing which fields can be used together.
This avoids the InvalidUnconstrainedJoin error and the cartesian product issue entirely, without requiring end users to add measures.
Thanks again for the insight — it helped confirm that this is fundamentally a data granularity/modeling issue, not something fixable purely through relationship settings.
Hi ImaneMeskine,
Thank you for the response and confirming that the issue is resolved now. Thank you for being part of Microsoft Fabric Community.
Thanks and regards,
Anjan Kumar Chippa
