SOLVED
Home

PivotTable Error with Relationships using Data Model

%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%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20817px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69138i232076E382399D88%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22relationship-tables.PNG%22%20title%3D%22relationship-tables.PNG%22%20%2F%3E%3C%2FSPAN%3E%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%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20540px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69139i5192E53FC19FEDE6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22desiredPivotTable.png%22%20title%3D%22desiredPivotTable.png%22%20%2F%3E%3C%2FSPAN%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%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20517px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F69140i599F4FE3D5FDA876%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22wrongPivotTable.png%22%20title%3D%22wrongPivotTable.png%22%20%2F%3E%3C%2FSPAN%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%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%3A)%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
Humberto_Herrera
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

 

6 Replies
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.

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.

Without (a sample of) your data this is very hard to answer.
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

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.

Hi Jan,

Thanks for your time.

 

I attached the data in the initial post.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies