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

 
image

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

 

image

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

image

Years Table:  One Row per Year

And now let’s relate them:

image

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:

image

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

image

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:

image

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:

image

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:

image

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

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

image

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.

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

  1. Geoff McNeely says:

    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!

    • powerpivotpro says:

      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.

    • powerpivotpro says:

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

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

  5. Anne says:

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

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

  7. Peter Albert says:

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

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

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

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

Leave a Comment or Question