Forum Discussion
error - cannot display the data because Power BI can't determine the relationship between 2 or more
Impactful IndividualSeconding nilendraFabric 's call for a proper star schema being the best and most performant solution.
If you absolutely must* have a spaghetti schema (which is what you get when you have multiple relationship pathways), then you need to deactivate any relationships that force multiple pathways by unchecking the 'Make relationship active' box.
If you then need to use that relationship in a measure you can use the DAX function USERELATIONSHIP to activiate the link just for that measure. So in you example you might deactivate all of the relationships to the customer dimension other than the one from the fact table.
* in most cases you will not need anything other than a star/galaxy** schema with maybe a bridge table.
** multiple stars (fact tables) in the same model.
If this helps, please consider Accepting as a Solution to help others find it more easily.
Hi,
If there is no relationship between the parent and child, the fact table will contain duplicate records.
What is the expected DDL for dimension tables when using a composite primary key?
Could you provide a sample DDL(Or reference document) for both the dimension tables and the fact table?
How should we load data into the dimension tables and fact tables? Can we use natural keys when loading data into the dimension tables and fact tables? Alternatively, can we use natural keys in the dimension tables and surrogate keys (SK) as foreign keys, maintaining them only in the fact tables? If yes, how can we avoid cross joins in the fact table?
We need to implement SCD Type 2 in the dimension tables.
Would adding a bridge table with a composite primary key on custid and cust_qid satisfy the requirements?
We will load data into the dimension tables and fact tables, The schema model will take care in the PowerBI? what is the best practice for createing dimension tables, fact tables, schema model and generate power bi reports?
Thank you
- v-csrikanth
Community Support
Hi fabric_no1
Sorry for the late response.To resolve Power BI relationship issues, adopt a star schema by avoiding snowflake schemas where dimension tables reference other dimensions. Dimension tables should be denormalized, and only the fact table should contain foreign keys to dimension tables, ensuring clear one-to-many relationships.Keep only one active relationship between tables. Deactivate others and use the USERELATIONSHIP() function in DAX measures to activate them when needed, preventing ambiguity in visuals.
Use unique surrogate keys in dimension tables and reference these keys in the fact table. Avoid natural or composite keys that can cause duplication or ambiguous joins.
Set relationships with the correct direction (usually single) and ensure matching data types with no nulls in key columns to maintain integrity.
Avoid mixing non-aggregated columns from different fact tables in the same visual. Instead, use measures or bridge tables to relate multiple fact tables properly.
For large datasets, enable incremental refresh to improve performance and manage data efficiently.
This concise approach will help fix the "Power BI can’t determine the relationship" error and optimize your data model.
If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.
