Relationships within PowerPivot don't appear to be locking in.

[DWig]

Hey all,

I have several tables, including "Company", "Projects", and "GLSummary".

Company_Key is the unique identifier that ties these three tables together. Related lookup column is "Company_Key" in table Company (arrows point from other two tables to Company).

Starting with an easy example:
When I make a pivot table out of this, I put Company_Key from table Company in the rows, and simply add Company_Key from table Projects beneath it. Rather than listing company keys with the matching value from the Projects table, it lists the company key from Company table, and all iterations of the company key field from Projects beneath each one. For example, if the company keys were as follows:

1005
1413
1937
1964

Instead of listing this way:
1005

1005​

1413

1413​

1937

1937​

1964

1964​

It lists this way:
1005

1005​

1413​

1937​

1964​

1413

1005​

1413​

1937​

1964​

1937

1005​

1413​

1937​

1964​

1964

1005​

1413​

1937​

1964​

Now obviously, I'm not simply trying to match the two lists. I'm trying to filter data by project which is attached (and functioning properly) to Company_Key in table Company. However, this is the simplest example I can think of to explain that the columns don't appear to be recognizing each other. I'm sure I've set up the relationships incorrectly, but I can't find where I've made my mistake.

 

[powerpivotpro]

What you are describing will ALWAYS happen, at least until you place something in the Values area of the pivot. It doesn't filter out the "nonexistent" combinations until something is on Values.

I'm hopeful that is your fix here. If not, check back

 

[DWig]

Rob,

Thanks for your response. My fix was actually a little more complicated than that. My problem was I was trying to link GL_Summary to Project through Company in a two-stage relationship, and Company has no way of knowing which project to assign it to.

Likewise, I couldn't go through Project to Company either, because there is activity in Company that doesn't necessarily have to have a project.

My fix was linking GL_Summary to both. Which is what I should have done in the first place.

Thank you for the insight, though. Love your book.

 

[DWig]

Rob,

Thanks for your response. My fix was actually a little more complicated than that. My problem was I was trying to link GL_Summary to Project through Company in a two-stage relationship, and Company has no way of knowing which project to assign it to.

Likewise, I couldn't go through Project to Company either, because there is activity in Company that doesn't necessarily have to have a project.

My fix was linking GL_Summary to both. Which is what I should have done in the first place.

Thank you for the insight, though. Love your book.