Using EXCEPT in Calculated member

I have within an MDX query the following calculated member:

MEMBER [Asset].[C开发者_运维知识库lass].[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.

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
    )
)