01-20-2019 10:19 PM
01-20-2019 10:19 PM
I have these 2 related tables by the CodEjeEstrategico field:
What I need to get is this PivotTable result:
However, all of the data of the Table1 is repeated in every row of the Table 2, as you can see in the following image:
And I don't know how to solve it, I really hope you can help me. This is the same problem I have with another Excel which has more related tables and much more data.
I'm attaching the file.
Thank you very much in advance.
01-21-2019 01:34 AM
01-21-2019 02:45 AM
Thanks for answering. In this case, the relationship is ok, given that one record or the table1 could be related with one or more than one record of the table 2, and by the way, it is the Excel that makes that kind of relation.
01-21-2019 07:46 AM - edited 01-21-2019 07:47 AMSolution
Most probably for you data like in this model
rows selected as
What to do is to add one more column into main table with related fielld
and make Pivot Table on it
or simply land the table back into the sheet with DAX expression like
(Data->Existing Connections->Open Table 1, right click on it, Table->Edit DAX->select DAX).
See in attached
01-21-2019 08:54 PM
Thank you very much for your answer Sergei,
I did it by adding a calculated column (in the table of the data model), applying the RELATED function, and making the corresponding pivot table on it.
At the beginning, I thought it was enough with having both related tables (with correct data) and then creating the pivot table in order to arrange the fields. However, it didn't work. The question I still have is why does this happen? Is it an Excel bug? I hope in the next Excel version it isn't necessary to add related fields :)
03-30-2020 04:27 AM
I have a similar issue as the one that has opened this post.
However, in my case, it is not possible to create a RELATED column,because entities are linked by date, and a more complex network of relationships.
I am having the same issue of getting a Pivot Table with repeated labels, that do not belong to the specific headings.
This is the relationships diagram:
Bellow, the relationship list:
This are the fields I am using to generate the Pivot Table from the Data Model:
And this is the pivot table I get as a result, where lots of entries appear as blank in a category where they don't even exist (all labels in that column appear repeted inside every single level from the previous category):