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