I am attempting to build a report to identify users with only one name attached to an account.
Normally, I would use count([Name ID] for [Account]), but it doesn't appear this is useful in this instance. Each user can be attached to multiple accounts in this particular instance.
I've also tried using count([Name ID] for [Account]) and setting Aggregate to Count Distinct and Rollup Aggregate to Count Distinct.
In addition, I've tried count(distinct([Name ID]) for [Account]).
None of these appears to return the data I am after though. Even with the aggregate set or the count distinct being used, I am still returning records where there are multiple Name IDs attached to a single Account when I try to filter by that value being equal to one.
What I'd ultimately like to do count the distinct Name IDs for the distinct Accounts if that is possible. I'm trying to find all Accounts that have only one Name attached for that specific Account value. I have not yet found a way to achieve this.
Thanks in advance for any assistance you can provide,
sjdig
count ( distinct [name id] for [account] ) should be exactly what you're after, if I understand your description correctly. One thing you want to look out for when using relational is, do not try to aggregate *both* over the aggregate function of a data item and an aggregate function in the data item's definition. That tends to yield false results.
But I might just be misunderstanding your issue. If you could whip up some dummy raw data and the required results, that should help to analyze how to get what you want.
Hi hespora, I believe my issue might have been with the aggregation. I believe I have corrected the issue. Thank you for your advice regarding this.