I need to group two or more values into one. What is the right syntax ? WITH [DIMENSIONS].[GENDERCAL] AS [Gender].[H1].[Gender].Currentmember WHEN [Gender].[H1].[Gender].[F] THEN 'FEMALE' WHEN [G

Question

Question

Jacinto Busquets · Apr 6, 2023

#Analyzer #MDX #InterSystems IRIS BI (DeepSee)

How can I create a calculated member in Analyzer using MDX for the following logic?

WHEN 'F' THEN 'FEMALE'
WHEN 'Female' THEN 'FEMALE'
WHEN 'M' THEN 'MALE'
WHEN 'Male' THEN 'MALE'
WHEN 'U' THEN UNDIFFERENTIATED'
WHEN 'UN' THEN UNDIFFERENTIATED'
WHEN 'Undifferentiated' THEN UNDIFFERENTIATED'
ELSE 'OTHER'
END

Product version: IRIS 2022.3

Discussion (2)2

Log in or sign up to continue

score 1 · Answer 1 · 2023-04-07T09:51:34-04:00

Hi Jacinto,

If these values can be determined at the time you build or synchronize the cube, you could create a level (in Architect) with a source expression that uses $SELECT to determine the value based on the logic you described.

I think this might be closer to what you have in mind than using calculated members - you could, for example, display the four categories you are mapping values to on rows just by putting the level on rows. (If you use calculated members, I think you'd want to define one member for each of the categories you're mapping values to.)

score 0 · Answer 2 · 2023-04-07T14:31:36-04:00

Thank you Sam for your quick response. I am not part of the architecture team, so I was thinking that I can do a calculate dimension at least until the architecture team has the time to work on my requirements.