locked
Relationships not working RRS feed

  • Question

  • I was trying to create a relationship between two tables and, while it displays in the PowerPivot console window, Excel keeps telling me a Relationship was needed.  After hitting many dead ends, I create two worksheets in excel to test a very simple relationship between two tables (shown below).   Once I created the relationship in the PowerPivot window, I tried to see if the relationship worked in Excel.  Still no luck - No Relationship Detected.  Please help:

    Table 1:  Locations

    ID       Location

    ---      ------------

    1     TX

     2     FL

     3     IL

     4     IN

     5     CA

    Table 2: Name

    ID        Name

    ---        -------------

    1     Eddie

    2     Mark

    3     Sam

    4     Mack

    5     Steve

     

     

    Friday, March 19, 2010 3:46 PM

Answers

  • Hi, Joe,

    Based on your data, I assume the two tables are related via the ID column. So using the sample data, I created two linked tables inside the PowerPivot workbook, and created the relationship with Location table as the lookup table, ie Location is on the one side and Name is on the Many side of the one-to-many relationship. With such workbook, when create Pivot table, you can put Location from the Location table on the row label, and count of Name from the Name table, this would return 1 for the count as expected. When you place Name on the row label, and Count of Location in the Value field, it would return 5 for each name and you get the Relationship may be needed warning message. This is by design as we do not support many to many relation in this release of the product.

    To workaround this, you can create a calculated column inside Name table using the following formula

    =COUNTROWS(CALCULATETABLE(VALUES('Location'[Location])))

    And place this calcuated column in the Value field in Pivot table to find the count of location for each name.

    Please let me know if you have any question on this.

    Thanks,

    Lisa

     

    Friday, March 19, 2010 7:16 PM

All replies

  • Hi, Joe,

    Based on your data, I assume the two tables are related via the ID column. So using the sample data, I created two linked tables inside the PowerPivot workbook, and created the relationship with Location table as the lookup table, ie Location is on the one side and Name is on the Many side of the one-to-many relationship. With such workbook, when create Pivot table, you can put Location from the Location table on the row label, and count of Name from the Name table, this would return 1 for the count as expected. When you place Name on the row label, and Count of Location in the Value field, it would return 5 for each name and you get the Relationship may be needed warning message. This is by design as we do not support many to many relation in this release of the product.

    To workaround this, you can create a calculated column inside Name table using the following formula

    =COUNTROWS(CALCULATETABLE(VALUES('Location'[Location])))

    And place this calcuated column in the Value field in Pivot table to find the count of location for each name.

    Please let me know if you have any question on this.

    Thanks,

    Lisa

     

    Friday, March 19, 2010 7:16 PM
  • Thank you Lisa.  I didn't realize that the product currently doesn't support many-to-many relationships.  :(   When do you think that enhancement will be available? 

     

    Thanks,

    Joe

    Wednesday, March 24, 2010 3:49 PM
  • Hi, Joe,

    The feature is currently being considered for the next version Powerpivot for Excel.

    Thanks!

    Lisa

    Friday, March 26, 2010 3:38 PM