Get hours spent on a specific theme

I’d like to create a calculated member that sums the hours spent on a specific theme within the current time interval.
The rows represent weeks.
What would be the correct filter for that?

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Hours spent on Business initiative",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Hours spent]","[Measures].[Hours spent on Business initiative]"],"members":[{"depth":0,"name":"Hours spent on Business initiative","full_name":"[Measures].[Hours spent on Business initiative]","format_string":"#,##0.00","calculated":true,"report_specific":true,"removed":false},{"depth":0,"name":"FTE","full_name":"[Measures].[FTE]","format_string":"#,##0.00","calculated":true,"report_specific":true},{"depth":0,"name":"FTE excluding absences","full_name":"[Measures].[FTE excluding absences]","format_string":"#,##0.00","calculated":true,"report_specific":true},{"depth":0,"name":"Team price per week","full_name":"[Measures].[Team price per week]","format_string":"#,##0.00","calculated":true,"report_specific":true},{"depth":0,"name":"Max time available (h)","full_name":"[Measures].[Max time available (h)]","format_string":"#,##0.00","calculated":true,"report_specific":true},{"depth":0,"name":"Logged time % of max","full_name":"[Measures].[Logged time % of max]","format_string":"#,##0.00","calculated":true,"report_specific":true},{"depth":0,"name":"Team price per h (logged time)","full_name":"[Measures].[Team price per h (logged time)]","format_string":"#,##0.00","calculated":true,"report_specific":true},{"depth":0,"name":"Team price per h (story points)","full_name":"[Measures].[Team price per h (story points)]","format_string":"#,##0.00","calculated":true,"report_specific":true},{"depth":0,"name":"Cycle time","full_name":"[Measures].[Cycle time]","format_string":"#,##0.00","calculated":true,"report_specific":true}]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time.Weekly].[Since week 48 2022]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time.Weekly].[Week])","members":[],"bookmarked_members":[]}]},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all"}],"bookmarked_members":[],"current_page_members":["[Project].[All Projects]"]},{"name":"Time","duplicate":true,"selected_set":["[Time.Weekly].[Since week 48 2022]"],"members":[{"depth":0,"name":"Since week 48 2022","full_name":"[Time.Weekly].[Since week 48 2022]","dimension_hierarchy":"Weekly","calculated":true,"drillable":true,"expanded":false,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Time.Weekly].[Since week 48 2022]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[{"dimension":"Measures","name":"Hours spent on Business initiative","formula":"Sum(\n  Filter(\n    Descendants([Issue].CurrentMember, [Issue].[Issue]),\n    [Issue].CurrentMember.Get('Theme') = 'Business initiative'\n   ),\n  [Measures].[Hours spent]\n)\n","format_string":""}]}
  } ],
  "calculated_members": [{"name":"Since week 48 2022","dimension":"Time","dimension_hierarchy":"Weekly","formula":"Aggregate(\n  [Time.Weekly].[Week].DateMembersBetween('W48, Nov 28 2022', 'today')\n)","format_string":""}]
}

Solved it by importing the measure “Issue Theme” in the eazyBI account.

1 Like