Calculated Member as an existing measure filtered by both a tuple and a set

Question

I'm looking to create a calculated member with SQL Server Data Tools Analysis Services on an OLAP cube that combines the following filtering approaches:

Tuple

(
    [Enrolment Planning Actuals].[Year].&[1],
    [Enrolment Planning Actuals].[Attribute 1].&[Y],
    [Enrolment Planning Actuals].[Attribute 2].&[N],
    [Enrolment Planning Actuals].[Attribute 3].&[N],
    [Measures].[Count]
)

Set as a single member

SUM(    
    EXCEPT(
        [Enrolment Planning Actuals].[Year].[Year],
        {
            [Enrolment Planning Actuals].[Year].&[1]
        }
    ),
    [Measures].[Count]
)   

The [Enrolment Planning Actuals].[Year] has members of values 1, 2, 3, 4 and I essentially want the calculated member to provide [Measures].[Count] filtered on:

• Include all [Enrolment Planning Actuals].[Year] members EXCEPT [Enrolment Planning Actuals].[Year].&[1]
• [Enrolment Planning Actuals].[Attribute 1].&[Y]
• [Enrolment Planning Actuals].[Attribute 2].&[N]
• [Enrolment Planning Actuals].[Attribute 3].&[N]

I recognize the SUM function may be the wrong approach in attempting to combine these filters.

I found the accepted answer to the article MDX Calculated member filter by dimension attribute exceptionally helpful up to this point.

Perhaps fundamentally there is something wrong in this way of thinking. Open to suggestion, thank you.

Answer 1

You are almost there buddy.

Just extending on the concept of set and tuple, and adding on the concept of crossjoin or *, here is what should work:

WITH MEMBER Measures.YourCalculatedMember AS

SUM(
    EXCEPT(
            [Enrolment Planning Actuals].[Year].[Year],
            {
                [Enrolment Planning Actuals].[Year].&[1]
            }
          ) *
    [Enrolment Planning Actuals].[Attribute 1].&[Y] *
    [Enrolment Planning Actuals].[Attribute 2].&[N] *
    [Enrolment Planning Actuals].[Attribute 3].&[N]
    ,
    [Measures].[Count]
  )