The Dreaded “One to One” Relationship, or Why “Boaring” Movies Are Best

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:

image

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

image

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

image

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:

image

Link it by the Year column:

image

Go back to PowerPivot and slap Animal on there:

image

“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:

image

We Need Animals to Be the Lookup Table

That fixes the pivot:

image

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:

image

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:

image

Ox Years Win!  But Wait…

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

image

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])

image

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

8 Responses to The Dreaded “One to One” Relationship, or Why “Boaring” Movies Are Best

  1. Janet says:

    Rob, this is a VERY helpful post! This is an occasion when the error message does nothing but baffle.

  2. Alyssa says:

    Hi Rob
    I also am receiving the Relationship May be Needed error message, however in my case, my lookup table is a unique list of business unit Codes “RC Codes” and I have created a relationship with another table – This is a listing of employee names with various other data, including which ‘RC’ they belong to. My relationship is from the Employee Table and the related lookup table is the unique list of RC codes (many to one). I am attempting to limit the data returned in my pivot table by filtering on ‘RC’ from the unique RC list however when I drag any data from the ‘Employee’ list, I receive the error message. The data is in the same format (text), and there are no additional characters (like spaces) included in the RC list of either table (I have tested this using a vlookup in excel and the data returns fine). The unique RC list is used as a key for other data tables and these relationships are working well. Any ideas on this one? thank you for your time

    • powerpivotpro says:

      Sometimes when you write measures and accidentally “attach” them to the wrong table, you get this message as a false alarm. Are the numbers in the pivot correct?

  3. Dave A says:

    New to PowerPivot and ran into this same issue. So is the proposed solution to inject another table to relate the currently related fields prompting the issue at to allow me to determine the direction of the former relationship and not PowerPivot?

  4. Dave A says:

    That’s what I attempt but I get the “i” notice and the relationship default backs such that the Relationship Needed notice appears in the pivot table. I have a sample file I can send.

  5. Stacie says:

    What do you do when you try to reverse the relationship tables but it tells you that a relationship already exists between the tables – but doesn’t?

Leave a Reply