List issues by Epic by board by time dimension

I have a bit of a brain teaser that is causing me headaches. I’m working with a relatively small data set, and I need to list issues by month, by board, by epic that were Accepted (status, not resolution).

First, I’m having a bit of trouble understanding how the Time dimension interacts with the Issue dimension.

Would it would to create a calculated measure that displays a “1” if conditions are met and filter on that measure? The condition would look something like "CASE WHEN acceptance date month = current time hierarchy member THEN “1"”

Suggestions are welcome. This is for a monthly report that looks at previous month to see what was accepted, by Epic, by Board (team)

Hi @Jordan_Wilkinson,

The Time dimension is linked to other dimensions via measures showing the number of specific occasions taking place within a particular date, week, month, etc.
For example, the “Issues created” measure links issues to the issue creation date. The transition measures link issues to the timestamp of a specific transition.

If the “Accepted” is a standard issue status and not a resolution, you might look if the issue was moved to the specific status by checking if it had a transition within the selected (current) month (member of the Time dimension).

That would translate into the following MDX expression.

CASE WHEN
 ([Measures].[Transitions to status],
  [Transition Status].[Accepted]) >0
THEN
 1
END

If you have the Time dimension on report pages, you do not need to explicitly mention it in the tuple, as it already applies from the report context.

If you have a relatively small dataset or simple measures on the specific report, you might add a filter on rows based on the measure value as described here - filter rows by measure.

However, if your dataset grows or you perform resource-intensive calculations, you might build this condition into the relevant measures to reduce excessive load.
The expressions for the measures might then be as follows.

CASE WHEN
 ([Measures].[Transitions to status],
  [Transition Status].[Accepted]) >0
THEN
  <the primary expression for the measure>
END

You might use the Epic hierarchy from the Issue dimension to see the Epics that themselves or their Stories had the relevant transitions.
However, if you are only interested in a specific Issue Type getting “Accepted”, you might add that issue type to the report pages or into the condition.

CASE WHEN
 ([Measures].[Transitions to status],
--only the specific issue type
  [Issue Type].[Story],
  [Transition Status].[Accepted]) >0
THEN
 <the primary expression for the measure>
END

You might add the Sprints dimension to the report Rows or Pages to split or filter the data by the relevant board.

Regards,
Oskars / support@eazyBI.com

1 Like

Thank you for helping me validate how the time dimension interacts with different measures. I was able to simply use the below expression, filter on matches “1”, and add the Issue Type as a page filter.

(
[Measures].[Transitions to status issues count],
  [Transition Status].[Accepted]
)