Hi,
I try to get a list of tickets created by users who are not members of specific groups.
I tried to do it this way:
Aggregate(
Except(
[Reporter Group].[Group].Members,
{[Reporter Group].[jira-servicedesk-users],
[Reporter Group].[Servicedesk-L1],
[Reporter Group].[Servicedesk-L2],
[Reporter Group].[Infrastructure],
[Reporter Group].[Software]}
)
)
But then, it generates one row per group that the user is member of and so you get a higher value.
In this case, the user created 1 ticket but is member of 3 groups, so it shows a total of 3 instead of 1.
Kind regards,
Guillaume
1 Like
Hi @Guillaume_Lorquet
Dealing with multiple-value fields, like reporter user groups, might be tricky, as the issue is counted to all related user group members.
To get the issue counted only once for the calculated member top level value, use the distinct issues count measures, for instance, “Issues created count” instead of “Issues created”. Issue still will be counted to each user group displayed in the rows, but only once for the total rows, as in my example where “Customer support” member contains several user groups:
About the member “Others”. With multiple field values, function Except() won’t exclude all expected issues from the count: the issue will still be counted if the user also belongs to some other (not-excluded) user group. Would you mind describing what is the expected report you want to build? Do you want to exclude from the report any ticket that is reported by users who belong to excluded groups, even this same user belongs to the group which is not excluded. For instance, if a user belongs to two user groups, Servicedesk-L1 and Developers, and you exclude only group Servicedesk-L1, do you want the issue the user has reported to be counted or not?
IlzeLA
support@eazybi.com
Hi, thanks for your feedback! Good first step! “Issue created count” works like a charm.
But indeed, I still have “duplicate” in the way that users member of 2 groups from the list will appear 2 times.
Let say:
- “User A” is member of “Servicedesk-L1” AND “Developers”. He created 1 ticket.
- “User B” is only member of “Developers”. He created 1 ticket.
- “User A” will appear 2 times in the report, one time in “Servicedesk-L1” and another in “Developers”.
- “User B” will appear only once because he’s a member of only 1 group.
→ Goal is to ensure the “User A” who is a member of “Servicedesk-L1” or if a user is a member of one of the defined groups, it won’t appear in the “Others”.
For now, the result is great because it removed from the “Others” groups mentioned in my filter, but the user could be a member of other groups, so it still appears in the list.
Hope it helps to get a better understanding. If not, let me know and do a screen sharing.
Hi, do you have any other suggestions for this issue?
