Can someone help with User Defined Calculated Member Formula?

Hello Smart Team,
Is there a way to exclude issues (all issuetype) with “Junked” status using MDX?

As example:
Project = iPeach
Label: Ph_0.5


I need help to EXCLUDE anything that has status = “Junked”.
Can someone please help?

Hello @esther,

I am not really sure what you are trying to achieve, but here are two suggestions for what I believe you want to do :slight_smile:

Option #1 - more elegant

Use Aggregate and Except.

    --set of all statuses
    --list of exceptions
    { [Status].[Junked] }) 
Option #2 - less elegant
  • Step #1: create one calculated member (eg: “All-Issues”) returning all issues in iPeach project maybe with some additional filtering.

  • Step #2: create a second calculated member to return all issues which you want to exclude (eg: To-Exclude").

  • Step #3: create a third member:

[Issue].[All-Issues] - [Issue].[To-Exclude]

Let me know how this works!

1 Like

Thank you very much!

Hi @esther,

I recommend using the more elegant Option #1 using functions Aggregate() and Except() as @AlexandruN suggested. Mathematical operations like subtraction might work in specific reports but behave unpredictably in most cases.

Here are also ground rules for calculations: Calculated measures and members

When creating a calculated member in a dimension, use only members from one dimension where you create them. Don’t mix dimensions in calculated members! We recommend using an aggregate function for calculated members and do not perform arithmetical operations, like, multiplying, dividing, and subtracting in those calculations.

Zane /