I have an unusual report to report on issues filters on the first sprint the issue was placed in and not any subsequent sprint after the first sprint.
I can see the property that shows all sprints but can you filter on just the first sprint.
Hi @briantaylor
Try creating a new calculated measure using this formula
CASE WHEN
[Measures].[Sprint issues committed]>0
THEN
Order(
Filter(
Descendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),
[Measures].[Sprint issues committed]>0
),
(
[Measures].[Transition to first timestamp],
[Transition field].[Sprint]
),BASC
).item(0).GetCaption
END
It will find the first sprint issue was committed to.
Martins / eazyBI
I am getting an error when I create the custom Measure
I have attached the report definition
{
“cube_name”: “Issues”,
“cube_reports”: [ {
“name”: " Issues Ready for Beta Over Time",
“folder_name”: “Test”,
“result_view”: “table”,
“definition”: {“columns”:{“dimensions”:[{“name”:“Transition Status”,“selected_set”:["[Transition Status].[Ready For Beta]"],“members”:[],“bookmarked_members”:[{“depth”:1,“name”:“Ready For Beta”,“full_name”:"[Transition Status].[Ready For Beta]"}]},{“name”:“Measures”,“selected_set”:["[Measures].[Issues history]","[Measures].[Issue Story Points]","[Measures].[Issue Sprint]","[Measures].[Issue status]","[Measures].[Test First Sprint]"],“members”:[]}]},“rows”:{“dimensions”:[{“name”:“Issue Type”,“selected_set”:["[Issue Type].[Story]"],“members”:[],“bookmarked_members”:[{“depth”:1,“name”:“Story”,“full_name”:"[Issue Type].[Story]"}]},{“name”:“Issue”,“selected_set”:["[Issue].[All Issues]"],“members”:[{“depth”:0,“name”:“All Issues”,“full_name”:"[Issue].[All Issues]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false,“removed”:true},{“depth”:1,“name”:“UCL - Traffic”,“full_name”:"[Issue].[UCL - Traffic]",“drillable”:true,“key”:“UCLSOA”,“expanded”:true,“drilled_into”:false,“removed”:true,“parent_full_name”:"[Issue].[All Issues]"}],“bookmarked_members”:[]}],“filter_by”:{“conditions”:[{“expression”:["[Measures].[Issues history]","[Transition Status].[Ready For Beta]"],“operator”:"\u003e=",“value”:“0”},{“expression”:["[Measures].[Issue status]","[Transition Status].[Ready For Beta]"],“operator”:"\u003c\u003e",“value”:“Cancelled”}]},“nonempty_crossjoin”:true},“pages”:{“dimensions”:[{“name”:“Project”,“selected_set”:["[Project].DefaultMember"],“members”:[{“depth”:0,“name”:“All Projects”,“full_name”:"[Project].[All Projects]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“UCL - Traffic”,“full_name”:"[Project].[UCL - Traffic]",“drillable”:true,“key”:“UCLSOA”,“parent_full_name”:"[Project].[All Projects]"}],“bookmarked_members”:[],“current_page_members”:["[Project].[UCL - Traffic]"]},{“name”:“Sprint”,“selected_set”:["[Sprint].[Current Sprint]","[Sprint].[All Sprints]"],“members”:[{“depth”:0,“name”:“Current Sprint”,“full_name”:"[Sprint].[Current Sprint]",“calculated”:true,“drillable”:true,“dimension”:“Sprint”},{“depth”:0,“name”:“All Sprints”,“full_name”:"[Sprint].[All Sprints]",“drillable”:true,“type”:“all”,“expanded”:true,“drilled_into”:false},{“depth”:1,“name”:“UCL - Traffic”,“full_name”:"[Sprint].[UCL - Traffic]",“drillable”:true,“expanded”:true,“drilled_into”:false,“parent_full_name”:"[Sprint].[All Sprints]"}],“bookmarked_members”:[],“current_page_members”:["[Sprint].[Current Sprint]"]},{“name”:“Profields Owning Dev Manager”,“selected_set”:["[Profields Owning Dev Manager].[Owning Dev Managers]"],“members”:[{“depth”:0,“name”:“Owning Dev Managers”,“full_name”:"[Profields Owning Dev Manager].[Owning Dev Managers]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Profields Owning Dev Manager].[Owning Dev Managers]"]},{“name”:“Profields Project Team”,“selected_set”:["[Profields Project Team].[Project Teams]"],“members”:[{“depth”:0,“name”:“Project Teams”,“full_name”:"[Profields Project Team].[Project Teams]",“drillable”:true,“type”:“all”}],“bookmarked_members”:[],“current_page_members”:["[Profields Project Team].[Project Teams]"]}]},“options”:{“nonempty”:“columns”,“total”:“rows”},“view”:{“current”:“table”,“maximized”:false,“table”:{“row_dimension_headers”:{“Issue”:true,“Issue Type”:true},“freeze_header”:true}},“calculated_members”:[]}
} ],
“calculated_members”: [{“dimension”:“Measures”,“name”:“Issues history”,“format_string”:"#,##0",“formula”:“Cache(\n NonZero(Sum(PreviousPeriods([Time].CurrentHierarchyMember),\n Cache([Measures].[Transitions to]\n - [Measures].[Transitions from])\n ))\n + [Measures].[Transitions to]\n - [Measures].[Transitions from]\n)”},{“dimension”:“Measures”,“name”:“Issue status”,“format_string”:"",“formula”:"[Status].[Status].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Status ID’)\n)"},{“name”:“Issue Sprint”,“dimension”:“Measures”,“formula”:"[Sprint].[Sprint].getMemberNameByKey(\n [Issue].CurrentHierarchyMember.get(‘Sprint ID’)\n)\n",“format_string”:""},{“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”:“Issue Story Points”,“dimension”:“Measures”,“formula”:"[Issue].CurrentHierarchyMember.get(‘Story Points’)",“format_string”:"#,##0.00"},{“name”:“Current Sprint”,“dimension”:“Sprint”,“formula”:“Aggregate(\n Order(\n Filter(\n [Sprint].[Sprint].Members,\n NOT [Sprint].CurrentMember.getBoolean(‘Closed’)\n AND\n NOT isEmpty([Sprint].CurrentMember.get(‘Start date’))\n ), \n – ordering by start date across all boards\n [Sprint].CurrentMember.get(‘Start date’), BASC\n )\n)”,“format_string”:""},{“name”:“Test First Sprint”,“dimension”:“Measures”,“formula”:“CASE WHEN\n[Measures].[Sprint issues committed]\u003e0\nTHEN\nOrder(\nFilter(\nDescendants([Sprint].CurrentHierarchyMember,[Sprint].[Sprint]),\n[Measures].[Sprint issues committed]\u003e0\n),\n(\n [Measures].[Transition to first timestamp],\n [Transition field].[Sprint]\n),BASC\n).item(0).GetCaption\nEND”,“format_string”:""}]
}
Hi Briana,
The code from earlier was not working for calculated members from “Sprint” dimension
Try this code:
CASE WHEN
[Measures].[Sprint issues committed]>0
THEN
Order(
Filter(
[Sprint].[Sprint].Members,
[Measures].[Sprint issues committed]>0
),
(
[Measures].[Transition to first timestamp],
[Transition field].[Sprint]
),BASC
).item(0).GetCaption
END
Martins