locked
Relationship isn't recognized RRS feed

  • Question

  • I created a workbook with 2 linked tables,
    One contains:
    year type budget
    2009 field1 100 
    2009 field2 120

    the other contains the same but then by month
    Year Month type sales 
    2009 1 field1 19 
    2009 2 field1 18 
    2009 3 field1 7 
    2009 4 field1 10

    I then created a calculated field in the PowerPivot window joining the year and type with &, and named it Key, gives me identical keys in both tables and the same datatype (text).

    When i create a pivottable, powerpivot tries to create a relationship but was unable to create one (no relationships detected). When i manually create the one between keys a relationship isn't available either.

    Why doesn't PowerPivot recognise the 2 similar keys? and why can't i relate the two tables?



    Sunday, January 17, 2010 9:26 AM

Answers

  • I recreated my powerpivot sheet and it now works (from the same source). Seems like there is a bug somewhere. Too bad my origional workbook crashed :(. i'll try and see if I can recreate the error.

    Thanks guys :) I couldn't believe what i saw so I had to post it here !


    • Marked as answer by Mardin Pena Friday, March 5, 2010 8:48 PM
    Monday, January 18, 2010 8:42 AM

All replies

  • Kasper,

    I created two tables on a worksheet based your data, and created links to these tables in PowerPivot. In PowerPivot, I created a calculated field in each table named Key = [year]&[type] for the first table and [Year]&[type] for the second table. My tables names are simply Table1 and Table2 respectively. I created a relationship between Table2 (the many) to Table1 (the one). So Table:Table2  Lookup Column:Key; Related Lookup Table:Table1  Related Lookup Column:Key

    Then I created a PivotTable and dragged fields to Row and Value areas. I didn't have any relationship issues throughout the process. I can't think of what might be causing your problem.

    Sunday, January 17, 2010 7:37 PM
  • Yes, I think Kasper is playing a little joke on us here.
    Sunday, January 17, 2010 11:35 PM
  • I recreated my powerpivot sheet and it now works (from the same source). Seems like there is a bug somewhere. Too bad my origional workbook crashed :(. i'll try and see if I can recreate the error.

    Thanks guys :) I couldn't believe what i saw so I had to post it here !


    • Marked as answer by Mardin Pena Friday, March 5, 2010 8:48 PM
    Monday, January 18, 2010 8:42 AM
  • I ran into the same issue today with several datasets that I just refreshed.  Most of my links got corrupted, and I couldn't recreate a link to one of my tables, even though the fields are the same data types and have valid values.  I have a table with costs by Profit Center and a Profit Center table with Profit Center as the key field.  I tested to make certain I didn't have duplicates in my Profit Center table and that was not the case.  My workaround (which is utterly ridiculous, I know!) was to add a calculated column in my Profit Center table to recreate the Profit Center field values.  My formula is:

    =left(ProfitCenterTbl[Profit Center],len(ProfitCenterTbl[Profit Center]))

    After doing this, I was able to link this new field (which is obviously identical to the values in my original Profit Center field) to the other tables in my PowerPivot dataset. 

    Weird, and definitely not a joke! :)

    Beverly

    Wednesday, August 25, 2010 7:14 PM