Forum Discussion
InvalidUnconstrainedJoin — sibling tables sharing same hub
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,
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.
