Table relationships in Row label-Filter

[peter_72]

For some reason my relationships are ignored in the powerpivot row label filter.

I have 3 tables:

• Company: Contains name (Company::Name) and country (Company::Country) of car manufacturers. For example:
  • BMW:Germany
  • Mercedes:Germany
  • Toyota:***an
• Continent: Contains name and continent of countries
  • Germany:Europe
  • ***an:Asia
• A relationshiop between Company::Country and Continent::Country is defined.

Next I add the Continent::Country field to the row label filter of a powerpivot table in Excel. Afterwards I add the Company::Name Field as additional row label filter below the Continent::Country filter. I'd expect to see the car manufacturers per year.

But what I get is cross product of Company::Name * Continent::Country although the necessary relationsships are defined:

• Germany
  • BMW
  • Mercedes
  • Toyota
• ***an
  • BMW
  • Mercedes
  • Toyota

This does not ***pen if I use the Company::County field instead of Continent::Country field. Why?

 

[NickyvV]

Could you maybe post an example of your workbook (or just the contents of the tables) here?
What version of Excel and PowerPivot are you using?

 

[peter_72]

Unfortunatly I cannot post attachments You can message me your email adress if thats ok for you.

It's Excel 2010 and Powerpivot 2012 (11.0.2100.60).

 

[Laurent C]

Hi Peter,
Excel issues an MDX command to the PowerPivot model, which is an in-memory SSAS instance. This MDX command does a CROSSJOIN Crossjoin (MDX) of whatever attributes you drop on your rows (same works for columns). If attributes come from different tables (handled as dimensions), then you get what you would expect from a crossjoin. If these attributes come from the same dimension, then the engine will only return "those tuples that actually exist".
If you add a measure to your pivot table, this measure will be added to the WHERE clause of the MDX query. If you add more measures to your table, they will be put on ROWS. In both cases, the result of the CROSSJOIN will be filtered so only those tuples are returned for which at least one measure is defined.
A measure that comes from your Company table will only be defined for the tuples that exist in your company table. A basic measure that comes from the Continent table will always be defined.

 

[peter_72]

Here are the tables:

Company:
[TABLE="width: 148"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]VW[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]Mercedes[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]Ferrari[/TD]
[TD]Italy[/TD]
[/TR]
[TR]
[TD]Porsche[/TD]
[TD]Germany[/TD]
[/TR]
[TR]
[TD]Renault[/TD]
[TD]France[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]***an[/TD]
[/TR]
</tbody>[/TABLE]

Continent:
[TABLE="width: 164"]
<tbody>[TR]
[TD]Country[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]Germany[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]Italy[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]***an[/TD]
[TD]Asia[/TD]
[/TR]
[TR]
[TD]France[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]South Korea[/TD]
[TD]Asia[/TD]
[/TR]
</tbody>[/TABLE]

The result is as described a cross join between the two.

 

[NickyvV]

Thank you Peter for mailing the workbook.
After a quick look I believe Laurent is right. When you just enter the Company::Name and Continent::Country it performs a crossjoin. However, when you enter one of the measures in the Values area the Company::Name is filtered by their real values.. as present in the fact table. So I believe the behavior is right for the PivotTable.
As you're mentioning in your post:

I'd expect to see the car manufacturers per year.

You might want to add a date dimension to the tables to have your Company::Name filtered by year maybe and get the correct results?

HTH