Why do Relationships not work until you put at least one field in the "values" field of PivotTable

Question

I'm aware of this behaviour but searched long time for an explanation without success...so i thought maybe here someone can answer this question:

Does someone know the technical background for the fact that relationships in powerpivot only work in pivotTables as long as at least one field is included in "values" area?

THANKS for clarifications!

Answer 1

Hi JJ78

This is not a PowerPivot behavior;  it is a Pivot Table one.  It happens because by default, Excel does not select the option to 'show items with no data'.  In other words, if you right click the pivot table marked as 'OK', and select 'Pivot Table Options', then go to display and check the 'show items with no data on rows' option, you will then see all customers for all countries again.

By default, Excel auto-filters the values for which there is no data when you place a measure in the Values section (This is equivalent to the NON EMPTY clause in MDX).   When no measures are placed in the Values section, there is nothing to auto-filter on.

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 2

Hi Javier,

thanks for your explanations! but to me the behavior is still not absolutely clear.

>This is not a PowerPivot behavior;  it is a Pivot Table one.
I still think this is special to powerpivot! If you create a Pivot from a XL-Table, external SQL/CUbe Query or even a SINGLE-PowerPivot-Table the filtering works!

>By default, Excel auto-filters the values for which there is no data when you place a measure in the Values section (This is equivalent to the NON EMPTY >clause in MDX).   When no measures are placed in the Values section, there is nothing to auto-filter on.

Hm..so NONEmpty(Table1,Table2) isn't valid? Why can't PP use the relationship to detect the valid combinations without a measure?  Only  NonEmpty(Table1,Table2,Measure) is valid? In my basic understanding of powerpivot i thought every table.column can act as dimension-element OR measure..

ok up to now , my actual explanation to the initital question would be:
When no measures are placed in the Values Section, and data is distributed in PP on multiple tables, there is nothing to auto-filter/NonEmpty processing

Answer 3

Your 2nd figure shows that both Responsible and CID come from the same table. In this case, there's only 1-1 relationship between Responsbile and CID.

In your first figure, Name and Responsible come from two different tables and without the non-empty behavior, Excel has to display all possible combinations - which is a cross join of two columns - that's why you see more stuff.

HTH,

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights