One thing about data blending that you've now run into is that your secondary source returns aggregated results on the fields you blend on.
This 3 minute YouTube video by a Tableau Partner gives a really simple background on data blending. http://www.youtube.com/watch?v=3YwT57l7YM4
Jonathan Drummey mentions it at the bottom of this blog entry: http://drawingwithnumbers.artisart.org/tableau-data-blending-sparse-data-multiple-levels-of-granularity-and-improvements-in-version-8/
Hopefully those will help explain what is happening there.
Ok. I've read the homework... alas no lights have come on...
Your first example shows exactly what I would expect to happen. Is there a difference when using an extract?
For the values where there is a single lookup, why do the measures fail to show up?
In short, I'm still confused! (Using single syllable words might help...sigh)
Hello Jim, we've just upgraded to Tableau 8, and I ran into the exact situation you described:
I was taking Dollars from my primary source and trying to aggregate based on Category from my secondary, using Item Number as the linked field. The moment the Category field was in the view, the Dollars went blank.
This same series of actions worked great when aggregating Dollars from my primary source with Fiscal Month from a secondary source, using the transaction Date as the linked field.
Later, when working on the Dollars by Category problem, I discovered that the Dollars successfully aggregated by Category if there was more than one measure in the view (Measure Names/Values on the shelves). By adjusting the Measure Names filter so that only the original Dollars measure was in the view, I achieved my original objective.
Does anyone know why data blending worked easily with the Date field, but not with the Item field? Or, does anyone have a workaround besides using the Measure Names/Measure Values approach (which I've used only rarely when creating views)?