This is a follow on from feedback in discussion Exclude Results from Other Field in Select Excluded and Set Analysis: Show Excluded and Refine Results.
I have attached a sample QVD. I am convinced that the solution here is really quite simple but it escapes me.
I am trying to create a list of bankers where they have not done any work for my team. I have 2 data sources, 1 a list of all work by Request Id, with additional fields of banker name, year and month of work. The second source is a list of all bankers and the region they work in.
I have created a pivot table with an expression of IF(COUNT({$<Month=,Year=,[Banker Region]=>} [Request Id])<1,'1','0')
Without any filters, this expression returns the result of 1 for any bankers who have 0 request IDs in my first source (list of work) and 0 where they have done work. I then use exclude zero values in presentation tab to remove those bankers.
The problem I face is when I try to further refine the results, by selecting a particular region or year or month. The list of 'not worked' is fixed and does not change. I believe this is the impact of the $<[field]=> syntax. If I remove this and just use the If with no set analysis, the result is correct with no selection for year / month etc, but as soon as I make a selection, e.g. region = MET, then all bankers from all regions except MET are shown.
