Hi everyone,
I’m trying to create a measure that would produce a table where each issue appears exactly once, grouped by:
- its last Status (Column) as of the end of the selected Time period, and
- its Assignee (Row) as of the same point in time.
The table should also allow drilling further into custom fields (Columns only).
I’ve experimented with different approaches, using Tail, Sum, custom Assignee dimensions, etc. but I haven’t been able to get it working exactly as intended.
So far, the closest I’ve come is with this measure:
Sum(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
IIF([Measures].[Transitions to status] > 0, 1, NULL)
)
The problem is that issues still appear more than once. For example, one entry under the current Assignee and another under “Unassigned” (because they were unassigned when they first entered a status like Open).
Another shot was this one, but it also doesnt work and lists them under multiple assignees.
Cache(Sum(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
IIF([Measures].[Transitions to status] > 0 OR [Measures].[Transitions to assignee] > 0, 1, NULL)
))
What I need is a single entry per issue, showing them only under the assignee/status they had at the end of the selected Time period.
Has anyone built something similar or knows the best MDX pattern for this?
This is the table for reference, the measure is called “Anzahl”. For the current version we just deleted “unassigned” from it (used to be the first row), but this is resulting in a not 100% accurate report:
Thanks in advance for your help!
