Filtering by custom measure "Transition Date To Delivered"

Hi,

I currently have a table that display a list of jira tickets, and I have one date column based on a custom measure, defined like that “Transition Date To Delivered”:

([Measures].[Transition to status last date],
[Transition Status].[Delivered],
[Time].CurrentHierarchy.DefaultMember)

I wish a filter combobox (like the “page”) to filter on this custom field and not any other one.
It seems that the “Time” dimension is filtered on the ticket creation date.

Is it possible to have such filtering combobox, to filter on this custom measure ?

Thanks

Just for your information, here is an export of the report:

{
  "cube_name": "Issues",
  "cube_reports": [ {
     "name": "Delivered",
     "folder_name": "Reporting - Invoicing",
     "result_view": "table",
     "definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Average workdays to delivery]","[Measures].[Transition Date To Delivered]","[Measures].[Issue Partners]","[Measures].[Issue Domains Cluster]","[Measures].[Issue Partner Request]"],"members":[{"depth":0,"full_name":"[Measures].[Average workdays to delivery]","format_string":"#,##0.00","report_specific":true,"name":"Average workdays to delivery","calculated":true},{"depth":0,"full_name":"[Measures].[Transition Date To Delivered]","format_string":"yyyy-mm-dd","report_specific":true,"name":"Transition Date To Delivered","calculated":true}]}]},"rows":{"dimensions":[{"name":"Project","selected_set":["[Project].[Service Portfolio]"],"members":[],"bookmarked_members":[{"depth":1,"name":"Service Portfolio","full_name":"[Project].[Service Portfolio]","drillable":true,"key":"SP"}]},{"name":"Transition Status","selected_set":["[Transition Status].[Delivered]"],"members":[],"bookmarked_members":[{"depth":1,"name":"Delivered","full_name":"[Transition Status].[Delivered]"}]},{"name":"Time","selected_set":["[Time].[Current month]"],"selected_set_expression":"DescendantsSet({{selected_set}}, [Time].[Month])","selected_set_action":[null],"members":[],"bookmarked_members":[]},{"name":"Partners","selected_set":["[Partners].[JV Gamewise]"],"members":[],"bookmarked_members":[]},{"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},{"depth":1,"name":"Service Portfolio","full_name":"[Issue].[Service Portfolio]","drillable":true,"key":"SP","expanded":true,"drilled_into":false,"parent_full_name":"[Issue].[All Issues]"}],"bookmarked_members":[]}],"order_by":{"expression":["[Measures].[Transition Date To Delivered]"],"order":"ASC"},"filter_by":{"conditions":[{"expression":["[Measures].[Transition Date To Delivered]"],"operator":"matches","value":".","value_type":"date","value_format_string":"yyyy-mm-dd hh:nn:ss"}],"others":false},"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Time","duplicate":true,"selected_set":["[Time].[Current month]","[Time].[Last 3 months]","[Time].[Last 6 months]","[Time].[Last 9 months]","[Time].[Previous month]"],"members":[{"depth":0,"name":"Current month","full_name":"[Time].[Current month]","annotations":{"group":"Default","predefined":"true"},"calculated":true,"drillable":true,"dimension":"Time"}],"bookmarked_members":[],"current_page_members":["[Time].[Current month]"]},{"name":"Partners","duplicate":true,"selected_set":["[Partners].[All Partners]","[Partners].[JV Gamewise]","[Partners].[JV Betca]","[Partners].[JV ESD]"],"members":[{"depth":0,"name":"All Partners","full_name":"[Partners].[All Partners]","drillable":true,"type":"all","expanded":true,"drilled_into":false,"removed":true},{"depth":0,"name":"JV Gamewise","full_name":"[Partners].[JV Gamewise]","calculated":true,"drillable":true,"dimension":"Partners"}],"bookmarked_members":[],"current_page_members":["[Partners].[JV Gamewise]"]}]},"options":{},"view":{"current":"table","maximized":false,"table":{"cell_formatting":{"[Measures].[Issue Partner Request]":{"type":"exact_value","whole_row":true,"rules":[{"exact_value":"Yes","background_color":"#F24B13"}]}},"freeze_header":true}},"calculated_members":[{"dimension":"Measures","name":"Average workdays to Market","formula":"/* 5 is average time for the deploy train */\nAvg(\n\n  Descendants([Issue].CurrentMember, [Issue].[Issue]),\n  [Measures].[Workdays to Market]\n  \n) + 5","format_string":"#,##0.00"},{"dimension":"Measures","name":"Percentile workdays to delivery","formula":"Percentile(\n\n  Descendants([Issue].CurrentMember, [Issue].[Issue]),\n  ([Measures].[Workdays in transition status],  [Transition Status].[Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Analysis]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Prioritization]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Delivery Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Progress])\n  , 75\n)","format_string":""},{"dimension":"Measures","name":"Max workdays to Market","formula":"Max(\n\n  Descendants([Issue].CurrentMember, [Issue].[Issue]),\n  ([Measures].[Workdays in transition status],  [Transition Status].[Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Analysis]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Prioritization]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Delivery Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Progress])\n\n) + 5","format_string":""},{"dimension":"Measures","name":"Average workdays to delivery - without outliers","formula":"Avg(\nFilter(\n  Descendants([Issue].CurrentMember, [Issue].[Issue]),\n  (([Measures].[Workdays in transition status],  [Transition Status].[Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Analysis]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Prioritization]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Delivery Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Progress])) \u003c 50\n),\n\n  ([Measures].[Workdays in transition status],  [Transition Status].[Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Analysis]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Prioritization]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Delivery Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Progress])\n\n)","format_string":""},{"dimension":"Measures","name":"Standard Deviation","formula":"Stdev(\n\n  Descendants([Issue].CurrentMember, [Issue].[Issue]),\n  \n  [Measures].[Workdays to Market]\n\n)\n","format_string":"#,##0.00"},{"dimension":"Measures","name":"Standard Deviation Min","formula":"[Measures].[Average workdays to delivery] -\n\nStdev(\n\n  Descendants([Issue].CurrentMember, [Issue].[Issue]),\n  ([Measures].[Workdays in transition status],  [Transition Status].[Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Analysis]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Prioritization]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Delivery Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Progress])\n\n)","format_string":""},{"dimension":"Measures","name":"Min workdays to Market","formula":"Min(\n\n  Descendants([Issue].CurrentMember, [Issue].[Issue]),\n  ([Measures].[Workdays in transition status],  [Transition Status].[Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Analysis]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Prioritization]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[Delivery Backlog]) +\n  ([Measures].[Workdays in transition status],  [Transition Status].[In Progress])\n\n) + 5","format_string":""},{"dimension":"Measures","name":"Average No Outlier","formula":"Avg(\n  Filter(  \n    Descendants([Issue].CurrentMember, [Issue].[Issue]),\n\t\n\t  [Measures].[Workdays to Market] \u003c ([Measures].[Average workdays to Market] + [Measures].[Standard Deviation])\n  ),\n\t\n  [Measures].[Workdays to Market]\n)","format_string":""},{"dimension":"Measures","name":"Workdays to Market","formula":"/*([Measures].[Workdays in transition status],  [Transition Status].[Backlog]) +*/\n([Measures].[Workdays in transition status],  [Transition Status].[In Analysis]) +\n([Measures].[Workdays in transition status],  [Transition Status].[Prioritization]) +\n([Measures].[Workdays in transition status],  [Transition Status].[Delivery Backlog]) +\n([Measures].[Workdays in transition status],  [Transition Status].[In Progress])","format_string":""},{"dimension":"Measures","name":"Max Deviation","formula":"[Measures].[Average workdays to Market] + [Measures].[Standard Deviation]","format_string":""},{"dimension":"Measures","name":"Min Deviation","formula":"[Measures].[Average workdays to Market] - [Measures].[Average No Outlier]","format_string":""},{"dimension":"Measures","name":"Transition Date To Delivered","formula":"([Measures].[Transition to status last date],\n[Transition Status].[Delivered],\n[Time].CurrentHierarchy.DefaultMember)","format_string":"yyyy-mm-dd"}],"description":"The average time to deliver a change request, it also highlight the normal deviation."}
  } ],
  "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":"Time","name":"Current month","format_string":"","formula":"Aggregate({\n  [Time].[Month].CurrentDateMember\n})"},{"dimension":"Time","name":"Previous month","format_string":"","formula":"Aggregate({\n  [Time].[Month].CurrentDateMember.PrevMember\n})"},{"name":"Issue Domains Cluster","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Domains Cluster')","format_string":""},{"name":"Issue Partner Request","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Partner Request')","format_string":""},{"name":"Issue Partners","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('Partners')","format_string":"StringFormatter"},{"name":"Last 3 months","dimension":"Time","formula":"Aggregate(\n  [Time].[Month].DateMembersBetween('3 months ago', 'today')\n)","format_string":""},{"name":"Last 6 months","dimension":"Time","formula":"Aggregate(\n  [Time].[Month].DateMembersBetween('6 months ago', 'today')\n)","format_string":""},{"name":"Last 9 months","dimension":"Time","formula":"Aggregate(\n  [Time].[Month].DateMembersBetween('9 months ago', 'today')\n)","format_string":""},{"name":"Average workdays to Market","dimension":"Measures","formula":"/* 5 is average time for the deploy train */\nAvg(\n  Cache(\n  Filter(\n    Descendants([Issue].CurrentMember, [Issue].[Issue]),\n    DateInPeriod(\n      ([Measures].[Transition to status last date],[Transition Status].[Delivered]),\n      [Time].CurrentHierarchyMember\n    )\n  )\n  ),\n  \n  [Measures].[Workdays to Market]\n  \n) + 5","format_string":"#,##0"},{"name":"JV Gamewise","dimension":"Partners","formula":"Aggregate({ \n  [Partners].[Betly-ar.us],\n  [Partners].[Betly-oh.us],\n  [Partners].[Betly-tn.us]\n})","format_string":""},{"name":"JV Betca","dimension":"Partners","formula":"Aggregate({ \n  [Partners].[C.nl]\n})","format_string":""},{"name":"JV ESD","dimension":"Partners","formula":"Aggregate({ \n  [Partners].[ESD.pt],\n  [Partners].[ESD.pt-GGM]\n})\n})","format_string":""}]
}

Hi @cdemez

It seems that you may need to remove the [Time].CurrentHierarchy.DefaultMember line from your tuple, so that the measure responds to the selection in the Time dimension.

Please try the following formula:

([Measures].[Transition to status last date],
[Transition Status].[Delivered]

Let me know if this works as expected!
​Best regards,
​Nauris / eazyBI support

1 Like