“Relationship may be needed?” But I already have a relationship! What’s going on?


“Well I’m not quite ready for a relationship right now Lois.”

A confusing error getting you down?

I got an email this morning from a friend who was running into this problem:

PowerPivot says "Relationship may be needed" - but you do have a relationship.  What's going on?

Are you getting this warning in the field list,
even though you DO have a relationship in place?

What gives?  Let’s return to our recent movies example.

To help you diagnose this problem, should you run into it, first let’s get a simple example ready.

Two tables of data in PowerPivot:  Movies, and Years.



Movies Table:  One Row per Movie, Multiple Movies per Year


Years Table:  One Row per Year

And now let’s relate them:


Creating a Relationship Between Movies and Years Table.  Years is the Lookup Table.

Now Let’s Slap Some Fields on that Pivot!

Let’s Released to Rows, and US Population to Values:


This gives me the following pivot, AND the relationship warning:


I’m Getting the Relationship Warning and my Population is the Same for Every Year.
What Did I Do Wrong?

Quick Fix Option #1:  Use the other Year Field!

If I remove Movies[Released] from Rows and replace it with Years[Year], it all works:


All Good Now:  Different Population Each Year, and No Warning

Note, however, that if you add another field from the Movies table, like Film Name, back to the pivot, the warning returns:


I Drag Film Name to Rows, and Now Our Warning Is Back
(And I’m Pretty Sure Eminem Didn’t Make His Feature Film Debut in 1924)

Ugh!  What Gives Here?  Well let’s go to the next fix…

Quick Fix Option #2:  Use RELATED()!

Go back into PowerPivot and add a calculated column to your Movies table, one that “fetches” US Population from the lookup table into the Movies (data) table:


Calculated Column in Movies Table – Fetches Population From the Lookup Table

Now go back to the pivot and use THAT field on Values instead:


Yay!  Fixed Again! Smile

“DUDE!  Why is this so hard?”

Well, Lookup tables aren’t “meant” to contain numeric values.  They are “meant” to contain things like Color, and Product Line, Month, and Customer Last Name.

Lookup tables are “meant,” in other words, to contain fields that you place on Rows, Columns, Slicers, or Filters – never on Values.

I keep using the word “meant” in quotations, because in the real world, Lookup tables DO quite often contain numeric values.  This movies and years example above is a perfect instance, so we can pretty much throw the word “meant” out the window, can’t we?

For now, you can just take Quick Fixes 1 and 2 above at face value, and use them.  (I suspect, over time, that most people reading this will have arrived here via a search engine after encountering this very problem, and will be happy with the Quick Fixes in the heat of the moment.)

Next week I will cover one more example of this problem, AND provide a deeper explanation of why this all works the way it does.

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 16 Comments

  1. Geoff McNeely

    Yeah, I was seeing this when dealing with two related (extended) tables of data. Neither one was truly a “lookup” but it does matter which one I use for my “counting”. Good short fix!

    1. powerpivotpro

      Yeah, “when a lookup table really isn’t a lookup table.” I’ve gotta work that sentence in somewhere next week.

      Of course, by then I will have forgotten… ooh, look, there’s a squirrel outside! :)

  2. Chris Campbell

    Rob, great post. This comes up a lot and is sometimes hard to explain. For those of us that still think in dimensions and facts, it makes perfect sense. Breaking out of that mold and thinking tabularly (add that to the dictionary!) when designing a model can be a challenge. Remembering to use things like RELATED to get fields where they make sense from and end-user perspective makes the model much friendlier and more likely to be adopted.

    1. powerpivotpro

      Thanks Chris.

      Yeah, the more I think about this, the more I remember just how challenging it was before I learned to think this way, and how challenging it is to explain it. But once you get it, it’s hard to remember the time where you didn’t.

      I actually think there will be TWO posts next week on this.

  3. scott reachard (@swreachard)

    Changing my thought process from relational Joins to Context Propagation has been a challenge, but is key to DAX! @javiGuillen has some good posts on his blog on the topic.

  4. Amy Francis

    FABULOUS! Thanks so much – just had this problem yesterday. Great fix.

  5. Anne

    I’d love to have a go at this. Anywhere I could get the raw data? looks great..

  6. David Hinckley

    You’re a lifesaver, Rob! Perfect post – short, clear, and helpful!

    1. powerpivotpro


  7. Peter Albert

    Hi Rob,

    I still don’t get this issue (and can’t find your follow up posts :-( ). Is this a bug in the 2010 version? Reading through your book you strongly advise against the use of the RELATED function. Isn’t there a way to get relationships with numeric IDs working???

    Thanks for any hint, Peter

  8. David

    I tried the related trick that you outline which looks tantalizing simple and then chews up hours of time when you get ” The column ‘tracker[Investment A]’ either doesn’t exist or doesn’t have a relationship to any table available in the current context.” or worse “The column ‘tracker[Price A]’ either doesn’t exist or doesn’t have a relationship to any table available in the current context.”

    I am getting tired of all this. I cannot see where you get the Powerpivot field list from as that looks like a useful way of finding where the relationships are?

  9. Michael

    Or you can use Tableau and it works out of the box!

    Thank you for the posting! I am also very tired of Microsoft’s BI stack shortcomings. And after over a year of being aware of the issue – it is not being fixed. Ridiculous!
    Use Tableau and you would not need lame explanations of why something should or should not work

  10. Ravin Singh D

    Thanks! I am stack on the problem past 4 days finally cleared. You are save me.

  11. Big Bear

    Hi, I use a calculation measure in my fact table that filters using an M2M scenario (for grouped M2M scenarios). When I use my M2M table related to the bridge, not directly to the fact table, the calculation is correct but this message appears. Am I missing something?

  12. www.indzara.com

    I ran into this message today. This was helpful. Thanks for the article.

  13. Debbie

    This is helpful but still leaves me with a question….

    Using your example above, looking at the year 1967 — there are two films, and each are showing the US population of 198,712,056 — good. But the roll-up for the year is now displaying this as a SUM, which to me, seems incorrect. (same with any year that has more than one film listed.

    I have this exact issue on a report that I’m working on currently…

    Table MetricsByAgent — is my main table that stores daily metrics by Sales Agent

    Table MetricsByDay — is a related table that has metrics as the day level that are not a rollup of agent, but strictly a day-level metric/value.

    So if I have my report with the rows defined as:

    –> Sales Agent

    And for each row I want to show Agent Metrics and DAY metrics. So the AGENT metrics are correctly summing when report is collapsed for date

    But the day metrics are also doing the same… and so if on a single day (10/19/2015), I have 10 Sales Agents.. they each display “Daily Calls Received” (which is not per sales agent, but simply a total for the phone queue for the day). Each agent is correctly display the value of 100 calls.. But on the DAY roll-up is it showing 1000 calls (summing up the values that were per agent based on the RELATED) in the data model.

    Any help is appreciated

Leave a Comment or Question