locked
Joins - Am I being stupid or something? RRS feed

  • Question

  • Hello experts,

    I have a very simple scenario but I must be missing something obvious - can you help?

    I have two tables in excel, each one is linked to a PowerPivot table:

    Servers: (TableServers)

    Server HW Department
    Server1 Dell Server Accounts
    Server2 HP DL580 HR
    Server3 Dell Server Accounts

    Monthly Opex Charges: (TableOpex)

    HW Monthly Opex
    HP DL580  £               500.00
    Dell Server  £            3,000.00

    What I want to see is the total charge per department.

    I created a relationship/join in PowerPivot like this:

    Table:                   Column:
    TableServers     HW

    Related Lookup Table:     Related Lookup Column:
    TableOpex                    HW

    However, when I create the PivotTable from PowerPivot I get this!!!

    Row Labels Sum of Monthly Opex
    Accounts 3500
      Dell Server 3000
      HP DL580 500
    HR 3500
      Dell Server 3000
      HP DL580 500
    Grand Total 3500

    Totally wrong!!! It seems to be pulling every row in the opex table for every row in the server table.

    Have I gone mad or is ther a bug in PowerPivot? Can you replicate this? In real life I have 000s of servers so if it doesn't work for this tiny example I'm going to have to go back to vlookups in excel!

     

    :-(

     


    ___________
    • Edited by Xerxel Tuesday, May 24, 2011 8:01 AM Formatting wrong
    Tuesday, May 24, 2011 7:58 AM

Answers

  • You cannot simply drag [Monthly Opex] into Values. Assume you have created a relationship from TableServers to TableOpex, define a measure with the following expression will work for you.

     

    =CALCULATE(Sum(TableOpex[Monthly Opex]), TableServers)

    Tuesday, May 24, 2011 8:48 PM
  • Xerxel,

    Now we figured out the problem:

    In your model, PowerPivot thinks TableOpex is the lookup table since it has unique values. However, since this table contains the [numeric] values it 'should' be the fact table. PowerPivot cannot create relationship between these two tables (that it was able to create a wrong relationship in my current unleased build is a bug - we will fix that).

    Because of absence of a relationship, you have to use RELATED function to bring the data value ("Monthly Opex") over to your table (TableServers) to get what you want.

     

    Chu


    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, May 27, 2011 5:26 PM

All replies

  • Hi,

    I was able to reproduce your issue and it really seems to be a strange behaviour!

    I even created an Access database with 2 tables you provided and set relationships at the db level, then connected to it from PowerPivot and could see these relationships there. However, after making a PivotTable I can see a notification 'Relations may be needed'. So, it looks like there are no relations but they really are!

    It is a rather standard task and can be done with a simple query like

    SELECT Servers.Department, Servers.HW, Sum([Monthly Opex Charges]) AS [SumOfMonthly Opex]
    FROM [Monthly Opex Charges] INNER JOIN Servers ON [Monthly Opex Charges].HW = Servers.HW
    GROUP BY Servers.Department, Servers.HW
    

    if you want to retrieve a 'table' like you show in your post. And just by removing Servers.HW field from this query to retrieve the sum of OPex charges by departments.

    So, HW is a primary key of Monthly Opex Charges table and there is one-to-many relation with Servers table where HW is a foreign key. In a PivotTable you should choose Department and HW from Servers as Row Labels and Monthly Opex from Monthly Opex Charges as Values summarized by Sum. But it won't work despite of all has done correctly. I also can't understand what is going on here.

    Again, this can only be seen when you have no relations between 2 tables. But you have (and I have) but no difference is!


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, May 24, 2011 11:44 AM
  • Andrey,

    Thank you for taking the time to reproduce this. I agree - if this data was in Access or SQL I could write a simple view to get the data and just work with the results but unfortunately it's not.

    We are both having the same problem! Could this be a bug of PowerPivot?

    Can anyone else solve this one?

    Xerxel


    ___________
    Tuesday, May 24, 2011 12:44 PM
  • Xerxel, what build of PowerPivot are you using?

     

    I can repro this one - however, I solved the issue by deleting the relationship and let PowerPivot auto-create the relationship for me and the problem is gone! Even though afterward I checked the relationship it created is identical with the one I manually created!

    I'll file a bug for this and hopefully got it fixed in next CTP.

     

    Chu


    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, May 24, 2011 8:17 PM
  • You cannot simply drag [Monthly Opex] into Values. Assume you have created a relationship from TableServers to TableOpex, define a measure with the following expression will work for you.

     

    =CALCULATE(Sum(TableOpex[Monthly Opex]), TableServers)

    Tuesday, May 24, 2011 8:48 PM
  • Chu,

    even if I delete a relationship and allow to determine them automatically by clicking 'Create' in the notification, nothing changes. I can see a message that 'No relationships detected' and if I go to PowerPivot window and check them I see the same result - no relationships created.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Tuesday, May 24, 2011 8:54 PM
  • Hi Chu,

    I tried allowing PowerPivot to auto detect the relationship for me. I thought it would as the column names match. However, it doesn't for me. It says "no relationships detected". Same as Andrey.

    I'm running build 10.50.1747.0.  Is this the correct build number?

    Do you know when the next version is coming out/hotfixes? I'll try Jeffrey Wang's workaround in the meantime and see if it works.

    Xerxel.

     


    ___________
    Tuesday, May 24, 2011 9:20 PM
  • Thanks Jeffrey.

    Your work around gave me the value for one server on each row so I multiplied it by the count and this seems to work.

    Therefore the resulting measure is:

    CALCULATE(Sum(TableOpex[Monthly Opex])*COUNTA('TableServers'[HW]), TableServers)

    I also tried using RELATED but it said their were no related records. Surely this shouldn't be this difficult.

    I'll be looking out for Chu's bug fix! Thakns for your help. Tell me if I'm still missing something.

    Xerxel


    ___________
    Tuesday, May 24, 2011 10:12 PM
  • Don't hold your breath. Chu was trying something on the upcoming release of PowerPivot and found some bug there. It won't fix your problem.

    You are right Related function works as well, actually preferred, I misunderstood what you wanted. Define the measure like below

    SumX(TableServers, Related(TableOpex[Monthly Opex]))

     

     

    Wednesday, May 25, 2011 1:23 AM
  • Xerxel,

    Now we figured out the problem:

    In your model, PowerPivot thinks TableOpex is the lookup table since it has unique values. However, since this table contains the [numeric] values it 'should' be the fact table. PowerPivot cannot create relationship between these two tables (that it was able to create a wrong relationship in my current unleased build is a bug - we will fix that).

    Because of absence of a relationship, you have to use RELATED function to bring the data value ("Monthly Opex") over to your table (TableServers) to get what you want.

     

    Chu


    -- This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, May 27, 2011 5:26 PM