PowerPivot Relationship Issue/Question


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

    Thank you,


    • Edited by Andrew J Hess Wednesday, July 17, 2013 11:16 AM Update with images
    Tuesday, July 16, 2013 4:52 PM


  • as Long as you do not add any measure to your PivotTable it will do a crossjoin when you select columns from 2 different tables

    that is the Default-behaviour and cannot be chagned

    an Option for your Scenario may be to add all values from your "Table 1" also to your "Table 2" using calcuated columns and RELATED()-function

    once this is done you can hide "Table 1"

    as you now only have one table, it will only Show valid combinations even without using a measure


    - -

    • Marked as answer by Andrew J Hess Monday, July 22, 2013 2:48 PM
    Wednesday, July 17, 2013 8:21 PM