Reference Table

[brf10043]

So I have a table with support tickets.  for each support ticket there is an Submitter Group.  I also have a Reference table of Resolving Groups that has a RA Type which indicates which Resolving Agencies are Helpdesk and which ones are Non Help desk.  Example below.

   

So There is no flag column in the Submitter Group that indicates if the Submitter Group is an Helpdesk RA or not.  Also, in the Submitter Group data there are Groups that are not listed in the Reference table of Resolving Agencies.

These table a currently linked in the data model.

I am attempting to write an expression that counts Submitter Groups in the support ticket table that do not exist in the Reference Resolving Agency data. I can count tickets where the Submitter Group is an Helpdesk Resolving Agency and I can count Submitter Groups where they are Non Helpdesk Resolving Agencies, but I can't get to a count where the Submitter Resolving Agency didn't match a Resolving Agency in the Reference Resolving Agency table.

Ideas?

[sunny_talwar · Accepted solution]

What if you do this... count everything and subtract those groups where they don't have a RA Type?

Count([Submitter Groups]) - Count({<[RA Type] = {"*"}>}[Submitter Groups])

[brf10043]

Could the problem be that I am linking the two tables together and maybe should be outer joining the Xref table tot he Ticket table so I can actually get Null values?

[brf10043]

Thanks Sunny!  Once again.  That seems to have worked.