Relationship Issues Power Pivot

[Craigc3814]

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.

 

[Matt Allington]

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.

 

[Craigc3814]

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.