How to use 'in' when create caculated member with mdx

I have created new caculated member which was named as targetProject for project dimension, the list as below:

aggregate({
[project].[aproject],
[project].[bproject],
[project].[cproject]
})

Now I was trying to handle the logic is,
if the project in targetProject, then to get the total issues count.
if the project not in targetProject, then to caculate the issues count for all of resolved issues only.

I would like to know how to use the “in” function just as in other language to express which project belong to targetProject and which is not.

please help. thanks

HI @xinranmo

MDX doesn’t have an “IN” function. In your use case, I would suggest creating a conditional calculated measure that, based on regular expression, gets the appropriate results. Please have a look at the code below:

CASE WHEN
  [Project].CurrentMember.Name MATCHES '(DEMO 001|DEMO 002|DEMO 003)'
THEN
  val([Measures].[Issues created])
ELSE 
  val([Measures].[Issues resolved])
END

And the picture of a sample report:

Please have a look at the presentation materials my colleague Janis prepared for the eazyBI community days in Las Vegas - Translating JQL queries to MDX reports. You can find more of these in the Events section of the community site - https://community.eazybi.com/c/events.

Best,
Roberts // eazyBI support

Thanks a lot Roberts

Hi Roberts,

I am trying to define a caculated dimension member for project, which all of projects are not in target projects.
I don’t have the full list for target and not in target projects.
how to do it ?

It is working now…, thanks

1 Like