Relationship Help

[Waynym]

I've created a simple relationship example but it doesn't work as I would expect. The example has 2 simple tables: 1) Company and 2) Company_Group. A company group is the parent of one or more companies. I've joined them together by a Parent_Company_ID which exists in both tables (in Company_Group as the primary key and in Company as a foreign key).

When I create a pivottable with the Company Name from the Company table in the row area of the pivottable and the Parent Company Name from the Company_Group in the filter area and slice to a single parent company ... all the values for all companies remain in the row area even though only 4 of 18 should show as children of that Parent Company. Company_Group is one:many to Company ... shouldn't this relationship work? What am I doing wrong?

Tables & Relationship

Table 1: Company, Columns: Company ID, Company Name, Parent Company ID

Table 2: Company Group, Columns: Parent Company ID, Parent Company Name

Relationship: one-to-many from Company Group to Company on Parent Company ID

 

[akice]

It only hides row values when there is a measure in the "Values" area of a pivot. And by default hides row values when the measure returns blank.