Power Pivot Relationships failed

[Aquila]

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.

 

[GraH - Guido]

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?

 

[Chihiro]

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.

 

[GraH - Guido]

So basic....

 

[Aquila]

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.

 

[Chihiro]

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.

 

[NARAYANK991]

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?

Hi ,

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

Narayan

 

[GraH - Guido]

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.

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!

 

[Aquila]

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

 

[Aquila]

Thank you all for sharing.

 

[NARAYANK991]

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.

Hi ,

I am trying to understand this concept , after Chihiro's mention about this :

Dimension table columns should not be in value field.

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

 

[Chihiro]

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.

 

[NARAYANK991]

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.

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

 

[Chihiro]

So when 2 columns from related tables are both placed in Row label, there are no calculation done and defaults to Cartesian join.


If you have one of the column from Dimension table and have any value field from fact table...


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.

 

[NARAYANK991]

So when 2 columns from related tables are both placed in Row label, there are no calculation done and defaults to Cartesian join.

Hope this helps in your understanding.

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

 

[GraH - Guido]

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.

 

[Chihiro]

...Is this right ?

Correct. Otherwise, all possible values will be shown in Label field.