Forum Discussion
Relationship error when field placed in value but not label
I have four tables linked via three relationships. Any combination of fields from the Orders and Payments tables placed in the pivot table is fine. Placing any field from Customers or Products in values produces a relationship error. My original quest was to create a measure to multiply quantity from the Orders table by Unit Price from the Products table plus Shipping from the Orders table. In the process of experimenting to solve the problem, I discovered the same problem with the fields from the Customers table. I have CLEANed and TRIMmed the data. I even resorted to pasting all the data as values into a new workbook and starting over. Data types on the unique identifiers match. For the Product ID shown in the screenshots both are whole number.
I am well aware I can accomplish my original purpose in a few other ways. I was trying to learn something doing it this way. My goal in posting here is to understand the underlying problem, thereby growing my understanding of PowerPivot. Why do fields from two of the tables in the Data Model produce relationship errors when placed in values?
Thank you.
1 Reply
This error is not caused by CLEAN/TRIM, nor by anything in the data values themselves.
It happens because of how Power Pivot resolves implicit measures and what table a value field “belongs to” in the star schema.
You did nothing wrong with your relationships.
The issue is structural:
Dimension tables cannot reliably produce implicit aggregation measures when grouped by fact-table fields.
Use explicit measures whenever a Products or Customers field must appear in Values.
This happens to everyone once—they try to sum a dimension field, and Power Pivot refuses with a relationship warning.
Define a measure in Orders:
Total Value : =SUMX(Orders,Orders[Order Quantity] * RELATED(Products[Unit Price]) + Orders[Shipping])
This is the proper star-schema fact aggregation.
No errors. Works with any slicers.
Hope this helps you, if not please just ignore it.
* It is better to give a bad answer than to give no answer at all. The user has to decide what is OK for them.
