MDX calculated member to band a measure

Question

I am trying to add a calculated member to an MDX query which will create a set of "Age Bands" for people in my database. I have a measure which contains the age of each person.

I have started with this MDX

SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

I have another measure ([Measures].[Age]) which I would like to band up and return with the rows, so eg anyone aged 0-25, 26-50 etc

Here is what Ive tried

WITH SET [Age Band]
    AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50"))
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY {
    (
        [People].[Gender].[Gender],
        [People].[Some].[Other Attributes],
        [Age Band]
    )
  } ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

However the error I get is

The function expects a tuple set expression for the argument. A string or numeric expression was used.

What am I doing wrong? Is there an easier/better way to do this without chaining IIF commands?

Answer 1

If you would have the age as an attribute in the People dimension in addition to having it as measure, you could define your bands as calculated members of this attribute hierarchy (calculated members do not need to be calculated measures, they can be members of any hierarchy!):

WITH Member [People].[Age].[0-26] AS
            Aggregate([People].[Age].[0] : [People].[Age].[26])
     Member [People].[Age].[26-51] AS
            Aggregate([People].[Age].[26] : [People].[Age].[51])
     Member [People].[Age].[Over 50] AS
            Aggregate([People].[Age].[51] : null)
SELECT 
  NON EMPTY {[Measures].[Count of People]} ON COLUMNS,
  NON EMPTY 
    [People].[Gender].[Gender]
    *
    [People].[Some].[Other Attributes]
    *
    { [People].[Age].[0-26], [People].[Age].[26-51], [People].[Age].[Over 50] }
    ON ROWS
FROM [MyCube]
WHERE ( ... slicer here .... )

Please note that I kept the border ages 26 and 51 in two bands each, as the names of your bands seem to suggest to do this. You can do this in MDX, as each definition of a calculated member is independent of the others, and hence it is not forbidden to have intersections between calculated members. But you probably would opt to adapt the expressions and only include each age in one band.

The way you tried it does not work, as a set in MDX consists of tuples, while you deliver a string from your IIF expression.

Answer 2

Try

WITH Member [Age Band]

Instead of

WITH set[Age Band]

Since you are doing a calculation based on the [Measures].[Age]

Try the following and see if you can work with WITH MEMBER

with member [Age Band] 
AS IIF([Measures].[Age]<26,"0-26",IIF([Measures].[Age]<51,"26-51","Over 50"))

SELECT 
   {[Age Band]  } DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS,
   {
    (
         [People].[Gender].[Gender]
    )
  }  DIMENSION PROPERTIES member_caption ON ROWS
FROM  [MyCube]