I need to create a report that reports on a single client per case and calculates unduplicated counts of clients served.
The simplest of these reports is for gender. If there are two (or more) clients in the case, I need to report the gender of one of them. And if the client I report on has more than one case, I need to count that client only once in the report.
My first attempt was to do a distinct count of ClientID grouped by gender. That gets the unduplicated count but it counts every ClientID in a case, so if there's a male and a female client in the case, they get counted once as a male and again as a female (or as two females if there are two female clients, etc.). I have to count one or the other, not both.
I've attached a file that has screen shots in it of the report design, the Database Expert links, and the output.
I've tried all sorts of groupings including ClientID (the field is NameCardID), CaseID (the field is FileID), @Gender (a formula that returns "Male", "Female", or "Unknown"), and various combinations of the three.
Where do I go from here?