Calculated Measure Across 2 Different Fields

Questions & Answers
1

Hey there,

I’ve been trying to come up with a report for tracking our created vs resolved issues for specific types of issues. For most teams I’ve been able to use one of the fields to find what I need with the following MDX calculated field:

Aggregate(
  Except(
    [Mark ID].[Mark ID].Members, -- Dimension to aggregate
    { [Mark ID].[(none)] } -- List members to exclude
  )
)

Basically it gets me the issues that have that field populated with some value. There’s one team we have that also uses a Label that I need to use as an OR clause. The JQL to find the issues would be:

project = TEST AND "Mark ID" is not EMPTY OR labels = Purple

At first I tried adding the label field as a page dimension and creating the same Except MDX query, but that doesn’t work as the page filters are anded together. The next thought I had was to modify my original MDX query to include the Label measure, but I haven’t been successful in getting that to work. What’s the best way to re-create this JQL query in MDX or using the filters that are available in EazyBI?

2

@kungfuice

This is where the calculation gets complicated and it won’t be enough with the Aggregate (with except or without except) in one dimension to calculate report results.
Here you would need to run the formula through “Issue” dimension members to filter the issue set based on multiple conditions.
And the most common way would be using the “Descendants” function to iterate through Issue dimension members.
Such filtering would be created as new user defined calculated measure for the “Measures” dimension:

NonZero(
  Count(
    Filter(
    -- iterate through set of issues
      Descendants([Issue].CurrentHierarchyMember,[Issue].[Issue]),
      (
      Not IsEmpty([Measures].[Issue Mark ID])
      OR
      CoalesceEmpty([Measures].[Issue labels],"") matches "Purple"
      )
      AND
      [Measures].[Issues created]>0
    )
  )
)

If the “Project” dimension is used as a page filter and project “Test” is selected, the validation “Issues created >0” in that formula above should filter the calculation only for the selected project.

When selecting this new calculated measure in your report, make sure you enable the “Nonempty” cross join for report rows to optimize the query.

Btw, I would use parenthesis in Jira JQL to be more precise where AND operator ends and OR operator starts.

Martins / eazyBI