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?
Best Answer
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!):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.