Duplicate Row data in PowerPivot

[flexinau]

Hi all,

The Pivot Table I created is not producing the desired results.
I linked two tables (Table1 & Table2).

Table1 contains Customers for Jobs

Table2 contains Work Orders for Jobs


After adding Table1 and Table2 to the Data Model using Power Pivot toolbar and creating relationships using the Job number, it is producing a table with each Jobs number containing all Work Order numbers.


Do you guys know why the duplicates are coming up on the pivot table based on the link tables?

I have attached a copy of the Excel spreadsheet.

Thanks in advanced.
Pat

 

[Alex Blakenburg]

Can you send us a picture of the Power Pivot > Edit Relationships screen

 

[RoryA]

You need a value field in the pivot.

 

[flexinau]

Alex, Does this give enough information on the relationship?
Rory, I will try to enter values into the pivot table but i did not think it should make a difference whether values were there or not?

 

[Alex Blakenburg]

I was sceptical of Rory's statement but I should have know better.

Per Phil_Seamark (Microsoft employee at the time)
Re: Power BI 1 to many Relationship gives out wrong results
"Think of it more as a full outer join........You can then use your filtering to decide what you'd like to display."

The filtering is what the below does when you have them unticked and then include values in the Pivot Table.
(Show items with no data on rows - Off)

 

[RoryA]

I am deeply hurt and offended...

 

[flexinau]

Thanks Alex and Rory.
Solved.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.