locked
Relations not working RRS feed

  • Question

  • Hello,

    I am new to PowerPivot and I have a problem with relations.  I created a very simple model using two tables: CustInfo (List of customers) and CustOrders (List of Customer orders).  I have established my Relationship on CustID in both tables but when I create a PowerPivot table and pull on CustOrders.CustId + CustInfo.CustName, it pulls ALL Customer names from CustInfo table... not just the one that should be linked by the Relationship between the 2 tables !!

    Any ideas what could be wrong here ?

    I am using MS-Excel 2010 + PowerPivot v2 (based on SQL Server 2012).

    Thanks

    José

    Friday, October 24, 2014 5:44 PM

Answers

  • This is the appropriate behavior. Pivot tables typically populate their row fields by performing a cross-join on the tables, and then filtering out the rows that do not have results for the measure you've used.

    There is a much more thorough examination of this topic here.

    Edit: Pivot tables are a tool for aggregations and analysis, not query tools.

    • Edited by greggyb Friday, October 24, 2014 7:22 PM
    • Proposed as answer by Michael Amadi Saturday, October 25, 2014 11:59 PM
    • Marked as answer by Charlie Liao Monday, November 3, 2014 9:57 AM
    Friday, October 24, 2014 6:59 PM

All replies

  • I just realized that as soon as I use a feild in the "Values" section of my Pivot Table... the relations starts to work.  However, if I drag&drop CustOrders.CustID + CustInfo.CutsName in the row labels WITH NOTHING in the Values box of the pivot table... I see all Customer Names being linked to each individual CustInfo.CustID !!
    Friday, October 24, 2014 6:10 PM
  • This is the appropriate behavior. Pivot tables typically populate their row fields by performing a cross-join on the tables, and then filtering out the rows that do not have results for the measure you've used.

    There is a much more thorough examination of this topic here.

    Edit: Pivot tables are a tool for aggregations and analysis, not query tools.

    • Edited by greggyb Friday, October 24, 2014 7:22 PM
    • Proposed as answer by Michael Amadi Saturday, October 25, 2014 11:59 PM
    • Marked as answer by Charlie Liao Monday, November 3, 2014 9:57 AM
    Friday, October 24, 2014 6:59 PM