skip to Main Content


“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 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 20 Comments
  1. 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. 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. 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. 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. 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

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

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

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

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

  8. I have a follow-up question… Quick Fix #2 works for two tables that are related, but what if there are four tables: three tables (e.g., Movies, Albums, TV shows) linked to the Years table? That would require me to pull 3 separate “Values” columns (1 from each table), but the fields multiply at this point… Is there a way to fix this with additional relationships like Excel suggests?

  9. Thanks for the help, but…
    …really, it would be easier to explain if you said: Key fields CANNOT contain only numbers. Add a character to it (on all of the linked tables), and your relationships will start to work.

    I tryed using text formated column, with a number inside, and the relationships still doesn’t work.
    This is the most common problem when relating tables in Excel, and no website seems to talk about it.
    This FIX was hard to find, and came a long way around for the explanation.

    In the real world, there are ID numbers for mostly anything and anyone. Most of my tables have a number as the key field so, not sure why not be able to use it.

  10. I have an issue on the power pivot relationship, the relationship is already made, but when I drag the any field from dimension table and insert into the row label and then drag some amount field from fact table insert into the values,the filter is not applied at all, just dump blank in row label value and grand total amount value in values and it is displaying only one row. can anyone help me to resolve this issue. I would thanks in advance.

Leave a Comment or Question