skip to Main Content

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

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 13 Comments
  1. 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

    1. 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?

  2. 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?

  3. 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.

  4. 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?

  5. Thank you! This worked fine with Power BI, but with this trick I was capable of making the same model in Excel 2016 Pro Plus.

  6. Confusing example. It doesn’t seem like you created a 1-to-1 relationship, you just reversed the 1-to-many to many-to-one (inner-outer, out-inner). Is it possible to create a true 1-to-1 relationship? What not show the Diagram View so we can get a better idea of what’s going on between the tables? Or did I miss something?

  7. Thank you Rob for the great post.

    My filters are not flowing down hill from one data table to another data table through 1 to 1 relationship. I read your earlier post (I’m guilty that my “look-up” table contains numbers).

    In my master data table (which has rolling forecast data) I have dates, account codes, customer codes, forecast numbers and one number column for all forecast numbers [call this forecast table]. I have another separate file which contains comments on some of the accounts [comment file]. I have joined the forecast table [look-up table in this situation] by creating row ID as alpha-number code to create 1-to-1 relation with the comment table [data table]. Row ID in the comment table is a subset of Row ID in the forecast table [as there is comment on some accounts only].

    I expected that filters from forecast table will flow “down hill” and filter the comments table when I pivot fields from forecast table and comment fields form the comment table on the row area and forecast numbers in the value area. But all comments are repeated with all accounts [complete pivot does not fit the Excel sheet and I get the error message] and forecast numbers for each account repeats for all comments.

    What I am missing here? I also tried importing row ID from forecast table into comment table, and it maps correctly the row IDs. Thank you for you time.

Leave a Reply

Your email address will not be published. Required fields are marked *