SOLVED

PivotTable Error with Relationships using Data Model

%3CLINGO-SUB%20id%3D%22lingo-sub-323439%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-323439%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jan%2C%3C%2FP%3E%3CP%3EThanks%20for%20your%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20data%20in%20the%20initial%20post.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-323438%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-323438%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20very%20much%20for%20your%20answer%20Sergei%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20it%20by%20adding%20a%20calculated%20column%20(in%20the%20table%20of%20the%20data%20model)%2C%20applying%20the%26nbsp%3BRELATED%20function%2C%20and%20making%20the%20corresponding%20pivot%20table%20on%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAt%20the%20beginning%2C%20I%20thought%20it%20was%20enough%20with%20having%20both%20related%20tables%20(with%20correct%20data)%20and%20then%20creating%20the%20pivot%20table%20in%20order%20to%20arrange%20the%20fields.%20However%2C%20it%20didn't%20work.%20The%20question%20I%20still%20have%20is%20why%20does%20this%20happen%3F%20Is%20it%20an%20Excel%20bug%3F%20I%20hope%20in%20the%20next%20Excel%20version%20it%20isn't%20necessary%20to%20add%20related%20fields%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321628%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321628%22%20slang%3D%22en-US%22%3E%3CP%3EMost%20probably%20for%20you%20data%20like%20in%20this%20model%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20478px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69164iB28619C4400A251D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Erows%20selected%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20194px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69165i408E6A2277556CDE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWhat%20to%20do%20is%20to%20add%20one%20more%20column%20into%20main%20table%20with%20related%20fielld%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20393px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69166i8935375DE21A5F26%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20make%20Pivot%20Table%20on%20it%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20169px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69167i11EA4B2EFF7B1F6F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eor%20simply%20land%20the%20table%20back%20into%20the%20sheet%20with%20DAX%20expression%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20388px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69168i1D2A1B4704BD8934%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E(Data-%26gt%3BExisting%20Connections-%26gt%3BOpen%20Table%201%2C%20right%20click%20on%20it%2C%20Table-%26gt%3BEdit%20DAX-%26gt%3Bselect%20DAX).%3C%2FP%3E%0A%3CP%3ESee%20in%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321586%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321586%22%20slang%3D%22en-US%22%3EWithout%20(a%20sample%20of)%20your%20data%20this%20is%20very%20hard%20to%20answer.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321525%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321525%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jan%2C%3C%2FP%3E%3CP%3EThanks%20for%20answering.%26nbsp%3BIn%20this%20case%2C%20the%20relationship%20is%20ok%2C%20given%20that%20one%26nbsp%3Brecord%20or%20the%20table1%20could%20be%20related%20with%20one%20or%20more%20than%20one%20record%20of%20the%20table%202%2C%20and%20by%20the%20way%2C%26nbsp%3Bit%20is%20the%20Excel%20that%20makes%20that%20kind%20of%20relation.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321505%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321505%22%20slang%3D%22en-US%22%3EI%20expect%20you%20have%20the%20relationship%20in%20the%20wrong%20direction.%20Where%20it%20currently%20shows%20a%201%20(left-hand%20side%20of%20the%20arrow)%20it%20should%20show%20the%20*%20and%20vice%20versa.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264125%22%20slang%3D%22en-US%22%3ERe%3A%20PivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20have%20a%20similar%20issue%20as%20the%20one%20that%20has%20opened%20this%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20in%20my%20case%2C%20it%20is%20not%20possible%20to%20create%20a%20RELATED%20column%2Cbecause%20entities%20are%20linked%20by%20date%2C%20and%20a%20more%20complex%20network%20of%20relationships.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20the%20same%20issue%20of%20getting%20a%20Pivot%20Table%20with%20repeated%20labels%2C%20that%20do%20not%20belong%20to%20the%20specific%20headings.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20relationships%20diagram%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Relationships.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180486i22F63CB1234E70C3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Relationships.png%22%20alt%3D%22Relationships.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBellow%2C%20the%20relationship%20list%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Relationships%20List.png%22%20style%3D%22width%3A%20797px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180487iF3955A975996064F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Relationships%20List.png%22%20alt%3D%22Relationships%20List.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20are%20the%20fields%20I%20am%20using%20to%20generate%20the%20Pivot%20Table%20from%20the%20Data%20Model%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pivot%20fields.png%22%20style%3D%22width%3A%20688px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180489iF4170631F5A63C32%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Pivot%20fields.png%22%20alt%3D%22Pivot%20fields.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20this%20is%20the%20pivot%20table%20I%20get%20as%20a%20result%2C%20where%20lots%20of%20entries%20appear%20as%20blank%20in%20a%20category%20where%20they%20don't%20even%20exist%20(all%20labels%20in%20that%20column%20appear%20repeted%20inside%20every%20single%20level%20from%20the%20previous%20category)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Pivot%20Table.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180488i233A8DB910528949%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Pivot%20Table.PNG%22%20alt%3D%22Pivot%20Table.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-321453%22%20slang%3D%22en-US%22%3EPivotTable%20Error%20with%20Relationships%20using%20Data%20Model%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-321453%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%2C%3C%2FP%3E%3CP%3EI%20have%20these%202%20related%20tables%20by%20the%20CodEjeEstrategico%20field%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20need%20to%20get%20is%20this%20PivotTable%20result%3A%3C%2FP%3E%3CP%3E%3C%2FP%3E%3CP%3EHowever%2C%20all%20of%20the%20data%20of%20the%20Table1%20is%20repeated%20in%20every%20row%20of%20the%20Table%202%2C%20as%20you%20can%20see%20in%20the%20following%20image%3A%3C%2FP%3E%3CP%3E%3C%2FP%3E%3CP%3EAnd%20I%20don't%20know%20how%20to%20solve%20it%2C%20I%20really%20hope%20you%20can%20help%20me.%20This%20is%20the%20same%20problem%20I%20have%20with%20another%20Excel%20which%20has%26nbsp%3Bmore%20related%20tables%26nbsp%3Band%20much%20more%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20attaching%20the%20file.%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreetings%3C%2FP%3E%3CP%3EHumberto%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-321453%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

Hi guys,

I have these 2 related tables by the CodEjeEstrategico field:relationship-tables.PNG

 

What I need to get is this PivotTable result:

desiredPivotTable.png

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:

wrongPivotTable.png

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.

 

Greetings

Humberto

 

7 Replies
Highlighted
I expect you have the relationship in the wrong direction. Where it currently shows a 1 (left-hand side of the arrow) it should show the * and vice versa.
Highlighted

Hi Jan,

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.

Highlighted
Without (a sample of) your data this is very hard to answer.
Highlighted
Best Response confirmed by Humberto_Herrera (Occasional Contributor)
Solution

Most probably for you data like in this model

image.png

rows selected as

image.png

What to do is to add one more column into main table with related fielld

image.png

and make Pivot Table on it

image.png

or simply land the table back into the sheet with DAX expression like

image.png

(Data->Existing Connections->Open Table 1, right click on it, Table->Edit DAX->select DAX).

See in attached

Highlighted

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 :)

 

Thanks again.

Highlighted

Hi Jan,

Thanks for your time.

 

I attached the data in the initial post.

Highlighted

@Sergei Baklan 

 

Hi all,

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:

Relationships.png

 

 

Bellow, the relationship list:

 

Relationships List.png

 

This are the fields I am using to generate the Pivot Table from the Data Model:

 

Pivot fields.png

 

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):

Pivot Table.PNG