Hi everyone — I’m working on a report that returns the oldest issue in “In Progress” status. There’s a page filter applied to limit results to Bugs, Stories, and Tasks, but Epics are still being returned unexpectedly.
Here’s what I’ve tried so far:
- Adjusting the page filter to exclude Epics
- Adding explicit Epic exclusion in the custom measure
I believe the best approach is to exclude Epics directly in the filter logic within the custom measure, but I’m running into errors when attempting to do that.
I’d really appreciate any guidance! Below, I’ll include the current version of the report definition, custom measure that needs adjustment, and screenshot of report.
Thanks in advance!
Custom measure
-- SetToStr(
Order(
/*
* Copy/pasted this filter from the "Number of tickets in progress"
* measure
*/
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Issue].CurrentHierarchyMember.Get('Project key') = "COREXP"
AND
(
DateBetween(
[Time].CurrentHierarchyMember.StartDate,
CoalesceEmpty(
[Issue].CurrentHierarchyMember.Get("Issue Development cycle start date"),
Now()
),
CoalesceEmpty(
[Issue].CurrentHierarchyMember.Get("Issue Development cycle end date"),
Now()
)
) OR (
(
[Measures].[Issue status] = "In Progress"
OR [Measures].[Issue status] = "Development Complete"
OR [Measures].[Issue status] = "Testing"
)
AND
DateDiffMinutes(
[Measures].[Issue status updated date],
[Time].CurrentHierarchyMember.StartDate
) > 0
)
)
),
[Measures].[Issue Current In Progress Time],
DESC
)
.Item(0).Key
-- )
Report definition
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Oldest In Progress Ticket - v2",
"folder_name": "Core Exp Dashboards",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Oldest In Progress Age]","[Measures].[Oldest In Progress Ticket]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[Last 185 days]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time].[Day])","selected_set_action":[],"members":[],"bookmarked_members":[]}]},"pages":{"dimensions":[{"name":"Sprint Team","selected_set":["[Sprint Team].[Core Engr Mod Exp Team 1]"],"members":[{"depth":1,"name":"Core Engr Mod Exp Team 1","full_name":"[Sprint Team].[Core Engr Mod Exp Team 1]"}],"bookmarked_members":[{"depth":1,"name":"Core Engr Mod Exp Team 1","full_name":"[Sprint Team].[Core Engr Mod Exp Team 1]"}],"current_page_members":["[Sprint Team].[Core Engr Mod Exp Team 1]"]},{"name":"Issue Type","selected_set":["[Issue Type].[Bug, Story, Task]"],"members":[{"depth":0,"name":"Bug, Story, Task","full_name":"[Issue Type].[Bug, Story, Task]","calculated":true,"drillable":true,"dimension":"Issue Type","formula":"Aggregate({\n[Issue Type].[Bug], [Issue Type].[Story], [Issue Type].[Task]\n})","format_string":"","annotations":null,"dependent_calculated_members_count":0,"dependent_report_ids":[2373507,3666668]}],"bookmarked_members":[{"depth":2,"name":"Bug","full_name":"[Issue Type.By type].[Standard].[Bug]"},{"depth":2,"name":"Story","full_name":"[Issue Type.By type].[Standard].[Story]"},{"depth":2,"name":"Task","full_name":"[Issue Type.By type].[Standard].[Task]"},{"depth":1,"name":"Bug","full_name":"[Issue Type.By name].[Bug]","drillable":true},{"depth":1,"name":"Story","full_name":"[Issue Type.By name].[Story]","drillable":true},{"depth":2,"name":"Task","full_name":"[Issue Type.By name].[Task].[Task]"}],"current_page_members":["[Issue Type].[Bug, Story, Task]"]},{"name":"Time","duplicate":true,"selected_set":["[Time].[Last 185 days]"],"members":[{"depth":0,"dimension":"Time","name":"Last 185 days","formula":"Aggregate(\n [Time].[Day].DateMembersBetween('185 days ago', 'today')\n)","format_string":"","full_name":"[Time].[Last 185 days]","drillable":true,"annotations":null,"calculated":true}],"bookmarked_members":[],"current_page_members":["[Time].[Last 185 days]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[]}
} ],
"calculated_members": [{"dimension":"Measures","name":"Transition to status last date","format_string":"yyyy-mm-dd hh:nn:ss","formula":"TimestampToDate(\n [Measures].[Transition to status last timestamp]\n)"},{"dimension":"Measures","name":"Issue status updated date","format_string":"mmm dd yyyy","formula":"[Issue].CurrentHierarchyMember.get('Status updated at')"},{"dimension":"Measures","name":"Issue status","format_string":"","formula":"[Status].[Status].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get('Status ID')\n)"},{"name":"Bug, Story, Task","dimension":"Issue Type","formula":"Aggregate({\n[Issue Type].[Bug], [Issue Type].[Story], [Issue Type].[Task]\n})","format_string":""},{"name":"Oldest In Progress Age","dimension":"Measures","formula":"Max(\n /*\n * Copy/pasted this filter from the \"Number of tickets in progress\"\n * measure\n */\n Filter(\n Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),\n [Issue].CurrentHierarchyMember.Get('Project key') = \"COREXP\"\n AND\n (\n DateBetween(\n [Time].CurrentHierarchyMember.StartDate,\n CoalesceEmpty(\n [Issue].CurrentHierarchyMember.Get(\"Issue Development cycle start date\"),\n Now()\n ),\n CoalesceEmpty(\n [Issue].CurrentHierarchyMember.Get(\"Issue Development cycle end date\"),\n Now()\n )\n ) OR (\n (\n [Measures].[Issue status] = \"In Progress\"\n OR [Measures].[Issue status] = \"Development Complete\"\n OR [Measures].[Issue status] = \"Testing\"\n )\n AND\n DateDiffMinutes(\n [Measures].[Issue status updated date],\n [Time].CurrentHierarchyMember.StartDate\n ) \u003e 0\n ) \n )\n ),\n [Measures].[Issue Current In Progress Time]\n)","format_string":"#,##0.00"},{"name":"Issue Current In Progress Time","dimension":"Measures","formula":"/*\n* Cycle days is only calculated after\n* a certain point in a ticket's lifespan,\n* This first case is the case where we do not have that data,\n* so we have to entirely hack an estimate together\n*/\nCASE WHEN\n IsEmpty(\n [Issue].CurrentHierarchyMember.Get('Issue Development cycle days')\n )\nTHEN\n /*\n * When we don't have any cycle time data available,\n * we just use time in current status.\n * The hope is that this at least helps us catch stories\n * that have been in progress for a very long time.\n * But I would love to find a better measure!\n * Theoretically you could game this by just toggling tickets\n * into and out of \"In progress\" every few days before standup\n */\n DateDiffDays(\n [Measures].[Issue status updated date],\n [Time].CurrentHierarchyMember.StartDate\n )\n/*\n* This is the case where we do have some cycle time data,\n* but a ticket is still in progress,\n* so we have to add past cycle time to current time in status\n*/\nWHEN\n (\n [Measures].[Issue status] = \"In Progress\"\n OR [Measures].[Issue status] = \"Development Complete\"\n OR [Measures].[Issue status] = \"Testing\"\n )\nTHEN\n [Issue].CurrentHierarchyMember.Get('Issue Development cycle days')\n + \n DateDiffDays(\n CoalesceEmpty(\n [Issue].CurrentHierarchyMember.Get('Issue Development cycle current start date'),\n [Measures].[Transition to status last date]\n ),\n [Time].CurrentHierarchyMember.StartDate\n )\n/*\n* This is the case where the cycle has been completed,\n* but on the selected date it had not been completed yet,\n* so we have to subtract some days\n* off of the final computed cycle time\n*/\nWHEN\n DateDiffDays(\n [Time].CurrentHierarchyMember.StartDate,\n [Issue].CurrentHierarchyMember.get('Issue Development cycle end date')\n ) \u003e 0\nTHEN\n [Issue].CurrentHierarchyMember.Get('Issue Development cycle days')\n - \n DateDiffDays(\n [Time].CurrentHierarchyMember.StartDate,\n [Issue].CurrentHierarchyMember.get('Issue Development cycle end date')\n )\nELSE\n /*\n * Should this case ever happen??\n * If the whole cycle has been completed\n * then \"current in progress days\" is not relevant\n */\n [Issue].CurrentHierarchyMember.Get('Issue Development cycle days')\nEND","format_string":""},{"name":"Oldest In Progress Ticket","dimension":"Measures","formula":"-- SetToStr(\nOrder(\n /*\n * Copy/pasted this filter from the \"Number of tickets in progress\"\n * measure\n */\n Filter(\n Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),\n [Issue].CurrentHierarchyMember.Get('Project key') = \"COREXP\"\n AND\n (\n DateBetween(\n [Time].CurrentHierarchyMember.StartDate,\n CoalesceEmpty(\n [Issue].CurrentHierarchyMember.Get(\"Issue Development cycle start date\"),\n Now()\n ),\n CoalesceEmpty(\n [Issue].CurrentHierarchyMember.Get(\"Issue Development cycle end date\"),\n Now()\n )\n ) OR (\n (\n [Measures].[Issue status] = \"In Progress\"\n OR [Measures].[Issue status] = \"Development Complete\"\n OR [Measures].[Issue status] = \"Testing\"\n )\n AND\n DateDiffMinutes(\n [Measures].[Issue status updated date],\n [Time].CurrentHierarchyMember.StartDate\n ) \u003e 0\n ) \n )\n ),\n [Measures].[Issue Current In Progress Time],\n DESC\n)\n.Item(0).Key\n-- )","format_string":"StringFormatter"},{"name":"Last 185 days","dimension":"Time","formula":"Aggregate(\n [Time].[Day].DateMembersBetween('6 months ago', 'today')\n)","format_string":""}]
}
