counting unique value only in a filtering table help

Hi,

I need to count number of employee on each project we have. problem is some employee will work on different job in the same day.

I want to be able to filter: job 1 and return lets say 12 emp. then filter job 2 only and return 10 emp. but if I select all job as my filter it would return lets say 15 emp. as some of them worked on both job that day.

I think I would need a helper column to show 1 if unique and 0 for the duplicated value. Problem is every formula I tried will not work with the filtering option.

lets say I filter only job #2 then every emp. should have a 1 but some will have a 0 because they were also in job #1.


also if I can get this formula working would it also consider it in a pivot table?

see attached example