Assignee Groups calculated member

Questions & Answers
,
1

Hi,
The assignees and groups of the issues I import in the eazyBi account are as the following:
Assignee 1, Assignee 2, Assignee 3, Assignee 4
Group A, Group B, Group C, Group D

Assignee 1 in groups A,B,C,D
Assignee 2 in groups A,B
Assignee 3 in groups A,D
Assignee 4 in groups A,C

I want 2 new calculated members (with using the assignee group, if needed) that are as the following:

  1. NOT in group B or C AT-ALL [Assignee 3 only]
  2. in group (B or C) AT-LEAST [Assignee 1,2,4]

Can someone please help to achieve this?

2

Hi @elyashivg,

The user might belong to several groups. Therefore, the calculated member should be made in the Assignee dimension.

The expression for the second option might be as follows.

Aggregate(
 Filter([Assignee].[User].Members,
  CoalesceEmpty([Assignee].CurrentMember.get('Groups'), '')
   MATCHES '.*(^|,)groupB($|,).*'
  OR
   CoalesceEmpty([Assignee].CurrentMember.get('Groups'), '')
    MATCHES '.*(^|,)groupC($|,).*'
  )
)

Once you have created the calculated member for the second condition, there are two options for the first condition.

If you are only interested in the result, you might use subtraction. This approach will work faster, but you might not be able to expand the member to see its descendants.

The expression for the simplified version might be as follows.

[Assignee].DefaultMember
-
[Assignee].[<inclusion group name>]

However, if you want to have an option of seeing the members of the exclusion group, you might use a different expression. Please note that this might work slower on instances with a high number of users.

Aggregate(
 Except( 
  [Assignee].[User].Members,
  ChildrenSet([Assignee].[<inclusion group name>])
 )
)

Regards,
Oskars / support@eazyBI.com