Another Example of “Relationship May Be Needed” When You Do, In Fact, Have a Relationship

Let’s pick up the topic from last week and examine another instance where you might encounter the dreaded warning message above.

Let’s start with this simple pivot:


Which is a column from the Years table and a column from the Movies table:


But now I have a new table:  Animals, which simply lists, for each year, which Animal is specified by the Chinese Zodiac:


Hollywood Insiders Know That the Animals of the Chinese Zodiac Are
Incredibly Important to Box Office Revenues


Relating that to the Years Table

Before we can use that important data, we must link it to our Years table (yes, we could also link it to Movies, but for this example I’ll link to Years).

So I right-click the Year column in the Animals table, choose create relationship:


Link it by the Year column:


Go back to PowerPivot and slap Animal on there:


“Relationship may be needed” – and Consecutive Years (1972 and 1973)
Cannot Both Be Boar, So Something is Wrong

OK, What’s Wrong This Time?

We have our relationship backwards.

Short Version:  go back, edit the relationship, and swap the tables:


We Need Animals to Be the Lookup Table

That fixes the pivot:


No Warning in the Field List Anymore, and 12-Year Gaps
Between “Same Animal Years,” as Expected

Hey Wait, I Thought It Was Impossible to Get Relationships Backwards!

What happened to our friendly little warning?  You know, this one?

The relationship cannot be created in the requested direction.  When you click create, the direction of the relationship will be reversed.

Usually, when you get your relationship backwards,
PowerPivot detects that and fixes it for you.

But we didn’t get that friendly little “i” icon this time.

That’s because Year is unique in both the Years and Animals tables. 

Meaning, a single Year only shows up once in each table.

When each table is unique like that, PowerPivot cannot detect which direction is correct.

So, when each table is unique, also known as a “one to one” or “1:1” relationship, you MUST be careful about which direct you select.

Another Symptom of a Backward Relationship:  RELATED() is Broken

The column either doesn't exist or doesn't have a relationship to any table available in the current context.

If You Try a RELATED() Formula And Your 1:1 Relationship is Backwards, You Get This Error

Again, reverse the relationship and this error will go away:


No changes to the formula.  I just flipped the relationship and it’s fixed.

The Punchline:  “Boaring” Movies are Best!

For those of you who have been patiently awaiting the results of this super-important analysis, here you go:


Ox Years Win!  But Wait…

This is not fair though, because there are more Ox years in the movie data:


Ox and Rabbit have 5 years each, but Tiger only has 4 years.

So we need a box office per year measure!

[Avg Box Office] =
[Sum of Total Box Office] / DISTINCTCOUNT(Movies[Released])


See?  “Boaring” Movies are best!  Dragon movies, ironically, are worst.

In reality of course, this was a silly analysis…

1) My movie data set is just top-grossing movies, and is not corrected for inflation, so “older” years are woefully under-represented.

2) A given year may have been the best year ever for movies, but shared the wealth equally across a bunch of good movies, with a lack of any single blockbuster movie, in which case that year wouldn’t show up as doing well in this analysis.

3) And really, this was a silly question to begin with, but hey, I needed a sample data set that was 1:1 with years and this was the first thing that came to mind Smile