I have a report that is showing the percentage of parent (ie non-subtasks) committed to at the beginning of a sprint that we complete by the end of that sprint (I received help for the measure here). However, it seems like my “Completed committed issues” calculated measure is causing things to always time out even though I’m limiting things to this year (there aren’t that many tickets/sprints), and I cannot understand why. Attached is the exported definition of my report.
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Sprints completed as committed",
"folder_name": "2021 Engineering OKRs",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Committed issues]","[Measures].[Completed committed issues]"],"members":null}]},"rows":{"dimensions":[{"name":"Sprint","selected_set":["[Sprint].[All closed sprints]"],"members":null,"bookmarked_members":null}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Time","selected_set":["[Time].[Current year]"],"members":[{"depth":0,"name":"Current year","full_name":"[Time].[Current year]","annotations":{"group":"Default","predefined":"true"},"calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":null,"current_page_members":["[Time].[Current year]"]},{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all"}],"bookmarked_members":null,"current_page_members":["[Project].[All Projects]"]},{"name":"Sprint","duplicate":true,"selected_set":["[Sprint].[All closed sprints]"],"members":[{"depth":0,"name":"All closed sprints","full_name":"[Sprint].[All closed sprints]","annotations":{"predefined":"true"},"calculated":true,"drillable":true,"dimension":"Sprint","expanded":true,"drilled_into":false}],"bookmarked_members":null,"current_page_members":["[Sprint].[All closed sprints]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":null}
} ],
"calculated_members": [{"dimension":"Time","name":"Current year","format_string":"","formula":"Aggregate({\n [Time].[Year].CurrentDateMember\n})"},{"name":"All closed sprints","dimension":"Sprint","format_string":"","formula":"Aggregate(\n Order(\n Filter([Sprint].[Sprint].Members,\n [Sprint].CurrentMember.getBoolean('Closed') AND\n NOT IsEmpty([Sprint].CurrentMember.get('Complete date'))),\n [Sprint].CurrentMember.get('Start date'),\n BASC\n )\n)\n"},{"name":"Sprint Story Points at closing","dimension":"Measures","format_string":"#,##0.00","formula":"-- Story points from issues when a sprint was closed\n( [Measures].[Story Points added],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint issues committed","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Active],\n -- An issue was in a sprint at a sprint start time\n [Issue Sprint Status Change].[Future =\u003e Active]\n)\n"},{"name":"Sprint issues at closing","dimension":"Measures","format_string":"#,##0","formula":"( [Measures].[Transitions to],\n [Transition Field].[Sprint status],\n [Sprint Status].[Closed],\n -- An issue was in a sprint at closing\n [Issue Sprint Status Change].[Active =\u003e Closed]\n)\n"},{"name":"Sprint issues completed","dimension":"Measures","format_string":"#,##0","formula":"(\n [Measures].[Sprint issues at closing],\n [Transition Status.Category].[Done]\n)\n"},{"name":"Committed issues","dimension":"Measures","formula":"Sum(\n Filter(\n --Descendants([Issue].Currentmember, [Issue].[Issue]),\n [Issue type].[Issue type].Members,\n NOT [Issue type].CurrentMember.GetBoolean('Subtask')\n AND [Measures].[Sprint issues committed] \u003e 0\n -- Disregard 0 point tickets, which are for SDMs and aren't guaranteed to be worked on\n AND [Measures].[Sprint Story Points at closing] \u003c\u003e 0\n ),\n [Measures].[Sprint issues committed]\n)","format_string":""},{"name":"Completed committed issues","dimension":"Measures","formula":"Sum(\n Filter(\n Descendants([Issue].CurrentMember, [Issue].[Issue]),\n IsEmpty([Issue].CurrentHierarchyMember.Get('Parent issue key'))\n AND\n -- Was committed to the sprint\n [Measures].[Committed issues] \u003e 0\n ),\n [Measures].[Sprint issues completed]\n)","format_string":""}]
}