PowerPivot Relationship Confusion In Excel 2013

Hi Everyone,

This is a really basic PowerPivot question I was hoping someone could help me out with.

The workbook can be downloaded here: http://www.mediafire.com/view/3uywdd...-workbook.xlsx

I threw together some sample data, just to test things out. It's very basic, one table of "Customers" and another table with the 50 US State's and their corresponding abbreviations. In the "Customers" table, there is a column of state abbreviations, and in the "States" table there is a column of state abbreviations as well. I have a relationship set up between these two in PowerPivot.

Each "Customer" in the "Customers" table has a unique "User ID". In some states, there are multiple "Customers" (User ID's).

When I try to pull a PowerPivot Table off of these tables, it's showing me all the states as being associated with every user ID. It looks correct if I just pull in the "States" column and the "User ID" column from the "Customers" table...

image1.jpg

But as soon as I drag in the "Full State Name" column from the "States" table, it screws up the PowerPivot Table and shows all the state names being related to the state abbreviations, and all the User ID's as being related to all the states. (This isn't the full image of the table, only part of it, since the full image would be too large).

image2.jpg

I'm used to doing everything with VLOOKUP's, and seldom used PivotTables at all in the past. But it was my understanding that these new PowerPivots would eliminate much of the need for VLOOKUP's. I'm under the impression there is something simple here that I'm missing, lol.

Any help would be greatly appreciated, thanks!

I think I may have found the answer to this, or at least one possible answer - I needed to have a value of some sort, even if it's a bogus column of values. As soon as I add one, the table displays as intended. Although if there are any other methods to get this to work, I am open to suggestions. This sounds like a bit of hack to add a fake value to sum up, if I just wanted to join several tables of data into one view using PowerPivot instead of using VLOOKUP's.

Suppose I just wanted to build an overall view of data from several tables, with static information such as names, addresses, phone numbers, login credentials, and such - and there aren't any values that I need to calculate, for example.