How to get a list of all unique assignees for epics

Evan Giordanella_fgmdeu

I am trying to get a count of all assignees that are part of an Epic, or even a higher Hierarchy member as defined by Advanced Roadmaps.

I am using EasyBI to try to achieve this.

 

I have tried the following and I keep getting unusual counts and cannot figure out why.

Count(
Descendants([Assignee].CurrentMember, [Assignee].[User])
)

Count(
Filter(
Descendants([Assignee].CurrentMember,[Assignee].[User]),
[Measures].[Issues resolved] > 0
)
)

Further, If the total count can include all historic assignees even if not current that would be most ideal.

Any help will be great!

Roberts Čāčus - eazyBI

Hi @Evan Giordanella

You are using the Descendants() function well in both examples. The first one will return the count of all Assignee dimension members. So that one is not what you are after. The second formula will return the count of current assignees of resolved issues. That seems close to what you want.

To see the number of total unique assignees for the Epics and levels higher, I recommend a calculated measure with the formula below:

NonZero(Count(
  Filter(
    Descendants([Assignee].CurrentMember,[Assignee].[User]),
    -- not unassigned
    [Assignee].CurrentMember.Name <> '(unassigned)'
    AND
    -- issues were assigned to the assignee
    [Measures].[Transitions to assignee] > 0
  )
))

Technically, you could use the measure "Transitions to assignee", but that would include a user twice if the issue was assigned to them twice.

Then, to see the names of these assignees, you can use the Generate() function. See the suggested formula below:

Generate(
  Filter(
    Descendants([Assignee].CurrentMember,[Assignee].[User]),
    [Assignee].CurrentMember.Name <> '(unassigned)'
    AND
    [Measures].[Transitions to assignee] > 0
  ),
  [Assignee].CurrentMember.Name,", "
)

See how that would look in a table report below:

See more details about the mentioned eazyBI functions on our documentation page - https://docs.eazybi.com/eazybi/analyze-and-visualize/calculated-measures-and-members/mdx-function-reference.

 

Best,

Roberts // support@eazybi.com