1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Power Pivot Relationships failed

Discussion in 'Power Pivot, Power Map etc' started by Aquila, Jun 4, 2018.

  1. Aquila

    Aquila New Member

    Messages:
    19
    I have two tables, one employee and the other with the categories. I’ve created the relationships between them through the Id category field, and I can’t find the reason why it doesn’t work. I’m using Excel 2016. It’s a very simple thing but I can’t find the error. Attached the file. Thanks a lot.

    Attached Files:

  2. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    Hi Aquila,
    This is strange indeed, it seems the relationship is not accepted, while nothing is wrong with it.
    When adding a calculated column in "Legajos" with the related function, I get the correct Salary.
    Yet the implicit SUM measure returns a cartesian product. This happens when there is no relationship found. Indeed when inserting the pivot I do get a warning "relation may be needed". Though it is defined in the model.
    Yet trying to create an explicit measure returned me
    "The column 'Legajos[IdCateg]' either doesn't exist or doesn't have a relationship to any table available in the current context."

    I'm as puzzled as you by this. @Chihiro, @NARAYANK991, would you have a clue?
  3. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Basically, many side (i.e. fact table) should contain the values that you want to aggregate not the other way around (i.e. dimension table should not hold values to be aggregated). You could either use RELATED(), or transform tables using PowerQuery to get around this.

    Have read of article from Rob Collie for more detailed explanation.
    https://powerpivotpro.com/2013/05/r...i-already-have-a-relationship-whats-going-on/

    Edited: For clarity.
  4. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    So basic.... :oops:
  5. Aquila

    Aquila New Member

    Messages:
    19
    It's just something very basic, and that's what draws my attention: it doesn't work. Supposedly this should work easily just by establishing the relationship. I do it in Power BI and it works. Apart is common for a table of employees to get from the category table the salaries. I appreciate your help.
    I know I can fix it using Power Query, or through a measurement, but I'm not looking for a solution for this. I was preparing an exercise to show how to use relationships through a simple example and I gave myself this. I just wanted to raise it because I don't have an explanation.
    Last edited: Jun 5, 2018
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Read through the link I gave. It gives more detailed explanation on why.

    But short of it is...
    Dimension table columns should not be in value field. But in row/column field. Relationship itself is not the issue here. But how it’s used.
  7. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Sorry but I will not be able to contribute because I am still using Excel 2010.

    Narayan
  8. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    In PowerPivot relationship filter context flows down from dimension to fact table, as Rob Collie explains it. PowerBI knows about "upstream" as well. Though I knew this in theory, I must admit I was as very surprised, like you, I could not make it work without a measure or calculated column.
    Being fooled by one of the very, very basic concepts of PowerPivot:(. Unforgivable I missed that. It just show again that @Naranyank991, in another post, is right that you need to be very conscience of what you are doing over in PowerPivot. And thank you @Chihiro for the provided link, I did read before, auwch!
  9. Aquila

    Aquila New Member

    Messages:
    19
    Excellent Chihiro!!! Thanks a lot. I'm not an expert, but this problem made me feel like a beginner. :))
  10. Aquila

    Aquila New Member

    Messages:
    19
    Thank you all for sharing.
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    I am trying to understand this concept , after Chihiro's mention about this :
    I am attaching a workbook which has the same data as the workbook in this thread , but in an Excel 2010 workbook.

    As long as the idCateg field is placed in the Values area , the pivot table displays everything correctly.

    If this is not placed in the Values area , the pivot table does not display correctly.

    Any explanations ?

    Narayan

    Attached Files:

  12. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    I can't open model. But my guess is that you have Name from Fact table in Row Label, along with Detail/Salary from Dimension table.

    This will prevent aggregation of IdCateg. As it will force 1 to 1 relation of Name & IdCateg.

    EDIT: To be more precise, it's how PowerPivot displays result, null values are not displayed in Pivot, hence, all Detail that returns null for IdCateg will be removed from Pivot.

    Without it, it would create ambiguity through 1 to many direction in row label field.
    Last edited: Jun 8, 2018
  13. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Chihiro ,

    There are quite a few things you have mentioned which I do not understand.

    You are right that I have placed the Name , Detail and Salary fields , in that order , as Row Labels.

    As long as the IdCateg field is not placed in the Values area , there is no aggregation of the IdCateg field. But this is perfectly fine , since what we want is not an aggregation of the IdCateg field. A 1 to 1 relation of the Name and IdCateg fields is perfectly normal , since both are from the same table.

    If you cannot open my workbook , can you try this same action with the initial file uploaded by the OP ?

    Narayan
  14. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    So when 2 columns from related tables are both placed in Row label, there are no calculation done and defaults to Cartesian join.
    upload_2018-6-8_13-42-13.png

    If you have one of the column from Dimension table and have any value field from fact table...
    upload_2018-6-8_13-41-50.png

    Remember, unlike joins, relationship in model just defines "HOW" measures should be evaluated and does not transform the data. Row & Column fields does not evaluate any calculation. Therefore defaults to Cartesian join.

    Hope this helps in your understanding.
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi Chihiro ,

    It sure does , thanks.

    I assume this is how Power Pivot will work in every case. For any data processing to be done between 2 or more tables , there has to be one field from one of the fact tables in the Values area. Is this right ?

    Narayan
  16. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    Hi @Naranyank991,

    I was still busy replying to your previous message #11, but was struggling so I left it to think about it. You got me rather fooled with your Pivot version. Only after a more careful read of Chihiro's reply 12, it started to me sense to me too how come yours was working and mine not.
    I admit I tried to explain this in different ways, but had to come to the conclusion that I can't explain it very well, let alone better then Chihiro did. If only I was Italian...
    Yet, later this came to mind: filter context is defined by the pivot's coordinates (row a column sections) and flows down from dim to fact table to evaluate measures (value section) and how they are calculated. So you need a "measure" from a fact table before the relationship kicks in. Still I needed Chihiro's answer to understand how it "defaults to Cartesian join". I never paid any attention to this.
    In the end this thread just shows again that a simple concept has unexpected - at first sight - consequences and that one needs to think (in my case hard ;-)) about it before really understanding it. So Chihiro travelled the furthest distance towards Italy. Therefore thank you @Aquila for the initial question, thank you @Naranyank991 for insisting to understand and thank you @Chihiro for explaining it very clear.
    NARAYANK991 likes this.
  17. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,053
    Correct. Otherwise, all possible values will be shown in Label field.

Share This Page