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.

score 2 · Accepted Answer · answered Jul 17 '15 at 06:13

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

Thank you, this was exactly what I was trying to achieve. – samb0x Jul 20 '15 at 15:06 — samb0x, Jul 20 '15 at 15:06

score 1 · Answer 2 · answered Jul 17 '15 at 22:27

I think you could even just drop the measure and create the member on one of the Attribute hierarchies. Then you can use different measures against it:

WITH MEMBER [Enrolment Planning Actuals].[Attribute 1].[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]
  )
SELECT
   {
     [Measures].[Count],
     [Measures].[Revenue]
   } on 0,
   {  
     [Enrolment Planning Actuals].[Attribute 1].[YourCalculatedMember], 
     [Enrolment Planning Actuals].[Attribute 1].&[Y]
   } on 1
FROM [YourCube];

Thanks for your input as well, this is a new perspective for me that I may incorporate in other instances. — samb0x, Jul 20 '15 at 15:06

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

2 Answers2