Relationship Issues Power Pivot

Craigc3814

Board Regular
Joined
Mar 7, 2016
Messages
217
I have two tables one for every single work item that is currently open at my company. There is no duplicate work item numbers in this field. It gives me information such as activity type, open date, assigned to, maintenance type etc.

I then have a Timekeeping table that also has the work item number on it, there are duplicates on this table because if 7 people charged time to the same work item number it creates a row for each person. Or if the same person charged time to a work item number on multiple dates it creates a row for each date.

Not every item on the Open work table has had time charged to it yet (Most have not)

I am creating a relationship in PowerPivot between the open table and the timekeeping table on work item number.

When I create a pivot table I drag work item number from the Open table to Rows, when I go to put the employee names that have charged time to the work item numbers from the timekeeping table it puts EVERY single employee under the work item number whether they have charged time or not. It will sum the hours correctly when I drag Hours to the values section but it does not remove employees that did not charge time to a work item number. What am I doing wrong?

I used to have a merged query between the two tables and it worked perfectly. I am attempting to save space by going the relationship method but I cannot get it to work.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
The time keeping table won’t filter the open table. I would have thought once you add the hours, only employees with hours should remain.
 
Upvote 0
You're correct if I add in the hours it will filter to only the work items with hours. Its when I try to add in the count of work items from the work items table it ruins everything. So I assume the best way to do this is to merge the data in PQ? It worked when I had it like that, I was just trying to eliminate some extra tables.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top