I'm trying to understand building a relationship in PowerPivot. My data looks like this:
Table 1: Account Locations... a description table that contains a unique Account Location ID, and it's corresponding description.
Table 2: Query... a query from a SQL database that has a patient id, and their corresponding AccountLocation ID.
My goal is to attach the Account Location description value (from Table 1) to each Account Location line item from the Query (Table 2). I built a relationship
with 'Table 2 Query' as the Table value, 'Table 1 Account Locations' as the Related Lookup Table, and joined on the Account Location field. Essentially, all I am trying do do is a simple VLOOKUP.
Creating a pivot table with "Account Location" from 'Table 2 Query' and "Description" from 'Table 1 Account Location' incorrectly lists every Description
value found in 'Table 1 Account Location' for each of the Account Location values in the Query table... see Image 1.
However, when I add a count of a field from 'Table 2 Query' , the pivot table then functions correctly... see Image 2
I'm ok with adding the count to produce the correct description, I would just like to understand why it is needed and if there's something I can do differently to make the 1st method work.
Edited byAndrew J HessWednesday, July 17, 2013 11:16 AMUpdate with images
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.