Getting a count of issues that do not have a given link

Skye · June 2, 2022, 8:38am

Hi,

I’m trying to create a report that lists the counts of issues that do not link to a story issue type.
I am having trouble creating a measure that will sum all empty squares in a column for the given hierarchy, see image.

I’ve created a simple measure to set all of the empty squares to 1, though this does not sum correctly

Any help on this would be greatly appreciated, thank you!

roberts.cacus · June 16, 2022, 11:43am

Hi @Skye,

I recommend defining a calculated measure that determines the Issue dimension current level in such a case. In the “Issue” level, it should return the measure “Issues created” when it is not linked. At a “Project” or “All” level, it should sum up all the issues without the link. The formula could look similar to the one below:

CASE WHEN [Issue].CurrentHierarchyMember.Level.Name = "Issue"
THEN
  NonZero(IIf(
    IsEmpty([Issue].CurrentHierarchyMember.Get('Caused Bugs')),
    val([Measures].[Issues created]),NULL
  ))
ELSE
  Sum(
    Filter(
      Descendants([Issue].CurrentMember,[Issue].[Issue]),
      IsEmpty([Issue].CurrentHierarchyMember.Get('Caused Bugs'))
    ),
    [Measures].[Issues created]
  )
END

I used the issue link “Caused Bugs”. Update the formula to fit your use case. After that, the report could look similar to the one below:

I also recommend using the “Drill into measure by another dimension” functionality to have only the relevant measures distributed across columns - Create reports.

Best,
Roberts // support@eazybi.com