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.
Thanks in advance, any insight or experience on this kind of modeling challenge is really appreciated.
Hi ImaneMeskine
Projet + Commande BTE + SFP Client work because the model can determine a single record that flows between all three tables, starting at SFP Client, going through Project, and ending at Commande BTE.
If you were to replace Commande BTE with COmmande FTTO, now the model can no longer determine the path of a record because of the many to one relationship between Project and Commande FTTO.
This ERD makes me think that very little value can be attained by joining SFP Client to Commande FTTO.
You have two options here, A: rebuild your model as consolidated Fact and Dimension tables in a star schema, this will ensure that each fact table has all the required information needed for the story it is trying to tell.
The other option, if my assumption is correct and there is in fact no value in joining the tables in the example above, is to educate your users and inform them that even if it did work, it would not provide them the insights they are looking for.
7 Replies
Hi ImaneMeskine,
The root of your problem here seems to be your data model. It does not appear to be a star schema which is the gold standard for analytical reporting, where tables are organized into facts and dimensions.
Power BI needs to be able to resolve a relationship path between the tables that are being combined, and with your current data model, it seems that is not possible.
Having a proper data model is important, especially if end users are building reports directly on your semantic model.
Are you able to post an ERD diagram of your current semantic model so we can suggest how it may be restructured and built into a star schema model?Hello tayloramy,
thanks for looking into this! Here is the ERD of the current model structure:
All tables are purely functional with no measures. All child tables point to Projet via SingleDirection relationships except Modules SFP Ancrage and Modules SFP Client which are BothDirections.
What is interesting is that some combinations work perfectly fine while others fail. For example combining Projet + Commande BTE + SFP Client in the same visual works without any error. However combining Projet + Commande FTTO + Gouv or Projet + Commande FTTO + Task both generate the InvalidUnconstrainedJoin error.
Let me know if you need any additional details about the model.
Hi ImaneMeskine
Projet + Commande BTE + SFP Client work because the model can determine a single record that flows between all three tables, starting at SFP Client, going through Project, and ending at Commande BTE.
If you were to replace Commande BTE with COmmande FTTO, now the model can no longer determine the path of a record because of the many to one relationship between Project and Commande FTTO.
This ERD makes me think that very little value can be attained by joining SFP Client to Commande FTTO.
You have two options here, A: rebuild your model as consolidated Fact and Dimension tables in a star schema, this will ensure that each fact table has all the required information needed for the story it is trying to tell.
The other option, if my assumption is correct and there is in fact no value in joining the tables in the example above, is to educate your users and inform them that even if it did work, it would not provide them the insights they are looking for.
Hi ImaneMeskine,
I agree with tayloramy, star schema is the way. I am interested to see the ERD diagram too, to see the data types of the columns you are key-ing/joining on I have read elsewhere that this can be caused by a data type mismatch too.
Also, did you try posting this in the Power BI forum?
Please give Kudos if this was helpful.
Best,
Rajesh
What you're seeing is expected behavior rather than a relationship configuration issue.
InvalidUnconstrainedJoin occurs because Projet is acting as a shared lookup table between multiple fact-like tables. When a visual contains columns from two sibling tables (for example, Commande FTTO and Gouv) without a measure that defines how to aggregate them, the engine cannot determine a valid row-by-row join. Power BI intentionally blocks this because any automatic join would either be ambiguous or produce a Cartesian product.
The reason Commande BTE and Modules SFP work is likely that they truly have a 1:1 relationship (or effectively one row per project), so the engine can safely resolve the join.
Unfortunately, there isn't a relationship setting (Single/Both direction), USERELATIONSHIP, or DAX measure that can make arbitrary combinations of sibling fact tables work correctly for self-service users. Bidirectional filtering affects filter propagation, not how visuals generate row combinations.
From a modeling perspective, your options are:
Restructure the model into a proper star schema, with Projet as a dimension and each transactional table treated as a separate fact table that is analyzed through measures.
Merge the sibling tables in Power Query if they represent different attributes of the same business entity and are intended to be analyzed together at the same grain.
Create a single fact table (or a denormalized reporting table) if users need to freely mix columns from those tables in table or matrix visuals.
If the tables are at different grains, keep them separate and guide users to analyze them through measures rather than expecting row-level joins between sibling facts.
In short, I don't believe there is a model-level switch that allows unrestricted mixing of sibling fact tables in visuals while also avoiding InvalidUnconstrainedJoin and Cartesian products. If completely free self-service reporting is a requirement, the model usually needs to be redesigned so users aren't combining columns from multiple fact tables at different grains.
One question that would help confirm the best approach: what is the grain of each sibling table (one row per project, order, task, module, etc.)? That typically determines whether the right solution is merging tables, creating a reporting fact table, or redesigning the star schema.
Thank you so much for this detailed and clear explanation, this is exactly the kind of confirmation I needed. You are right that this is expected behavior and not a configuration issue I was missing.
To answer your question about the grain of each table: Commande FTTO and Task are m:1 meaning multiple rows per project, Gouv is also m:1 with multiple rows per project, while Commande BTE and Modules SFP are 1:1 meaning one row per project, which explains why those combinations work fine as you mentioned.
Given this, it sounds like the cleanest solution for our use case would be to redesign toward a proper star schema with Projet as a pure dimension and each transactional table analyzed through measures. The challenge is that our end users expect to freely drag and drop columns from different tables into table visuals without thinking about measures, which is a self-service requirement that seems fundamentally at odds with having multiple fact tables at different grains.
Would you say that in this kind of scenario — multiple fact tables at different grains sharing a common dimension, with free self-service reporting as a hard requirement — the only truly safe option is full denormalization in Power Query into a single reporting table? Or is there a star schema design that can still support column-level self-service without requiring users to always add a measure?
Thanks again for your time and expertise.
