Define new calculated measure -> #ERR: mondrian.olap.fun

Questions & Answers
,
1

Hello eazyBI community, cool to be here! :slight_smile:

I have a question defining a new calculated measure.

This is the current MDX-Expression:

Aggregate(
Filter(
[Issue].[Issue].Members,
DateDiffDays(
[Issue].CurrentHierarchyMember.get(‘Created at’),
[Issue.Epic].CurrentHierarchyMember.get(‘Target date’)
) < 0 AND DateDiffDays(
[Issue].CurrentHierarchyMember.get(‘Created at’),
[Issue.Epic].CurrentHierarchyMember.get(‘Target date’)
) <= -20*5
)
)

My goal is to show the number of tickets (of a specific issue type XY), which are created 20 weeks before the “Target date” of the parent epic.

My solution:

Pages:

  • Projects → to limit the results concerning a specific project

Rows:

  • Time (All times) → time should be limited by the MDX-Expression above
  • CustomField → not relevant, only to be more specific
  • Issue (Epic) → to show all epics

Measures:

  • Issue Type (XY) → becaue I only want to show the number of issue of the issue type XY
  • MY CUSTOM MDX EXPRESSION (see above) "Number of tickets (of issue type XY), which are created 20 weeks before the “Target date” of the partent epic.

The problem is get this error message:

grafik
grafik808×202 24.8 KB

Can someone help me?
Thank you in advance!
Best regards, Robin :slight_smile:

2

Hi,

The recommended solution to your report is to use the Epic level of the issue dimension in the report rows and create the count of issue like this:

NonZero(Count(
  Filter(
    [Issue.Epic].CurrentHierarchyMember.Children,
    [Measures].[Issue type]="Story"
    AND
    DateDiffDays(
      [Issue.Epic].CurrentHierarchyMember.get('Created at'),
      [Issue.Epic].CurrentHierarchyMember.Parent.get('Target date')
    )>100
  )
))

This formula counts the Stories under the Epic created more than 100 days before the Epic Target date.

image
image1420×1106 115 KB

Kindly,
Janis, eazyBI support

Kindly,

3

Hi Janis Plume,
thank you for your reply!
Your idea helped me to find a suitable solution. :raised_hands:
Now I get the results I was searching for
 if there wasn’t another but.
If I activate my new calculated measure, I can’t use any other filters.
Or rather, additional filters have no effect on the results as long as my calculated measure is activated.

For example:
My new calculated measure and another filter called “Year Indicator” are active.

Every epic has a customfield, which indicates a year.
Now I add the customfield “Year Indicator” as a filter for my table.
Like you can see in the picture. There is written “2023”.

grafik
grafik1021×174 12.3 KB

After I activate this filter there is no change in the table.
Is it possible that my new calculated measure restricts something or am I completely missing something here?
Thanks in advance!