Error "can't determine the relationship between fields", when adding a column to a report.

[ACECEO]

I am getting the error "can't determine the relationship between fields", when adding a column to a report.

I have the following data sets:

• Insured_Dependent
• Insured_Policy_Holder
• Insurance_Order_Details
• Insurance_Order
• Insurance_Policy

The ERD is shown below.

The relationships are shown below.

If I add any column from Insurance_Order_Details, Insurance_Order, or Insurance_Policy, I get the error.

This is a problem because I need to add colums from all of those.

What do I need to do?

Any guidance or recommendations would be appreciated.

[DataNinja777 · Accepted solution]

Hi @ACECEO 

It is important to distinguish dimension table and fact table in your data model.  

• Insured_Dependent  (Dimension table)
• Insured_Policy_Holder  (Dimension table)
• Insurance_Order_Details  (Fact table)
• Insurance_Order (Fact table)
• Insurance_Policy (Dimension table)

Instead of putting all the tables side by side as shown in your screen print, the best practice is to put the dimension tables above the fact tables so that you can clearly see the direction of filters flowing down from dimension tables to the fact tables (one side of the relationship to many side of the relationship). 

Best regards,  

[ACECEO]

Thanks. I completely overhauled the relationsips to fix the issue. I am all set now.