Using EXCEPT in Calculated member

Question

I have within an MDX query the following calculated member:

MEMBER [Asset].[Class].[Fixed Income Derivatives (Inflation Linked)]
AS
(
    [Asset].[Class].&[Fixed Income],
    [Asset].[Sub Class].&[Derivatives],
    [Asset].[Sub Class Type].&[Inflation]
)

This is used within a query as follows:

SELECT
{
  [Measures].[Market Value]
} ON 0,
NON EMPTY(
{ 
    [Asset].[Class].[Fixed Income Derivatives (Inflation Linked)]
} ON 1
FROM [Asset]

This works fine, and of course gives me the market value of all Inflation linked Fixed income derivatives.

I now try to add a second calculated member, this time to give me all fixed income derviatives which are NOT inflation linked. I though this would be as easy as an EXCEPT in a second member like so:

MEMBER [Asset].[Class].[Fixed Income Derivatives (Non Inflation Linked)]
AS
(
    [Asset].[Class].&[Fixed Income],
    [Asset].[Sub Class].&[Derivatives], 
    EXCEPT(
       [Asset].[Sub Class Type].[Sub Class Type], 
       [Asset].[Sub Class Type].&[Inflation]
    )
)

Alas, no luck - it yields the error

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

Which is a confusing message, but par for the course in SSAS. Im sure im on the right track, and just have the EXCEPT slightly wrong, but I cant for the life of me spot the problem.

Answer 1

You just need to use Aggregate function to achieve your goal without getting an error.

Change your calculated member definition as below;

MEMBER [Asset].[Class].[Fixed Income Derivatives (Non Inflation Linked)]
AS
(
    Aggregate
    (
       {[Asset].[Class].&[Fixed Income]} * 
       {[Asset].[Sub Class].&[Derivatives]} * 
       EXCEPT
       (
        [Asset].[Sub Class Type].[Sub Class Type], 
        [Asset].[Sub Class Type].&[Inflation]
       )
       ,Measures.CurrentMember
    )
)