error - cannot display the data because Power BI can't determine the relationship between 2 or more

[fabric_no1]

Hi,
We are encountering an error in the Power BI report: "Cannot display the data because Power BI can't determine the relationship between two or more fields." Should we modify the data model or adjust the data load in the fact table to resolve this? Any assistance would be greatly appreciated. Best Practices for Fact Tables, dimension table and data model in this scenario. 

Customer Dimension (Parent Table):
cust_sk, cust_id, cust_name, start_date, end_date, current_flag
1, 10, 'ABC', 03/25/2025, null, 'Y'
2, 11, 'TDH', 03/25/2025, null, 'Y'

Product Dimension (Child Table):
prod_sk, cust_sk, prod_id, prod_name, start_date, end_date, current_flag
80, 1, 20, 'Dove', 03/25/2025, null, 'Y'
81, 2, 21, 'RAM', 03/25/2025, null, 'Y'

Department Dimension (Child Table):
dept_sk, cust_sk, dept_id, dept_name, start_date, end_date, current_flag
91, 1, 30, 'soaps', 03/25/2025, null, 'Y'

Sales Fact (Fact Table):
sales_sk, cust_sk, prod_sk, dept_sk, start_date, end_date, current_flag
61, 1, 80, 91, 03/25/2025, null, 'Y'
62, 2, 81, 0, 03/25/2025, null, 'Y'

Thank you.

[spencer_sa]

Seconding @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.

[fabric_no1]

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

[nilendraFabric]

Hello @fabric_no1 

Your model doesn’t follow a conventional star schema pattern because both Product and Department (dimension tables) contain foreign keys (cust_sk) to the Customer dimension
• This creates a snowflake-like structure that can cause filter propagation issues and relationship ambiguity

Dimension tables should not typically contain foreign keys to other dimension tables
• The presence of cust_sk in Product and Department tables creates potentially confusing relationship paths

Few best practices;

Best Practices for Dimensional Modeling in Power BI
Fact Tables
• Should contain foreign keys to dimension tables and measurable metrics
• Typically have the largest number of rows in the model
• Consider incremental refresh for large fact tables
Dimension Tables
• Should contain a unique key and descriptive attributes
• Should be denormalized when possible to improve query performance
• Avoid snowflake dimensions unless absolutely necessary