Filtering measure by dimension causes similar behaviour to IgnoreUnrelatedDimensions = True in calculated member

Question

I'm trying to define a calculated member in SSRS using SSAS that is the sum of a measure filtered on a value of a dimension. This works fine, except when I view the calculated member in the browser by the dimension I filtered on. Instead of seeing null values where I would expect them, the grand total is repeated over the rows, similar to what happens when you set IgnoreUnrelatedDimensions to True on a measure group.

How can I fix this?

Example calculated member defined on the calculations tab in SSRS:

AGGREGATE({[Cow].[Hoof Location].[Front]}, [Measures].[Count])

This works fine when viewed against anything other than [Cow].[Hoof Location]. But when viewed by this dimension, Count is repeated across rows.

Answer 1

You can try

IIf([Cow].[Hoof Location].CurrentMember IS [Cow].[Hoof Location].[All],
    AGGREGATE({[Cow].[Hoof Location].[Front]}, [Measures].[Count]),
    NULL
   )

Possibly you have to adapt the name of the All member to the name it has in your cube.

Answer 2

Here is an MDX calculated measure that uses trick that you want:

I used my base, so please substitute [Create Date].[Create Date].[Year].&[2014] with your own set and [Create Date].[Create Date].[Month] with your level of dimension [Cow]. Here is a standard 'Year-Month-Day' dimension.

CREATE MEMBER CURRENTCUBE.[Measures].[TestAgg]
 AS
    IIF(
        Intersect(
            [Create Date].[Create Date].CurrentMember
            ,Exists(
                Descendants(
                    [Create Date].[Create Date].[Year].&[2014]
                    ,[Create Date].[Create Date].[Month]
                    ,SELF_BEFORE_AFTER
                )
            )
        ).Count > 0
        ,[Measures].[Count]
        ,IIF(
            [Create Date].[Create Date].CurrentMember is [Create Date].[Create Date].[All]
            ,AGGREGATE(
                {[Create Date].[Create Date].[Year].&[2014]}
                ,[Measures].[Count])
            ,null
        )
    ),
VISIBLE = 1;

Here is some explanation:

1. I used Descendants to organize the set of necessary members.
2. Intersect is used to understand whether the current member of the dimension is under this filter condition.
3. Then the first IIF is used to show blanks in other years.
4. The last IIF is used for the total.

And some pictures of this measure's behavior:

If you want the same value on each level of 2014 and its children (but blanks for others), use

AGGREGATE(
    {Exists(
        Descendants(
            [Create Date].[Create Date].[Year].&[2014],
            [Create Date].[Create Date].[Month],
            AFTER)
        )
    },
    [Measures].[Count]
)

or SUM. But I guess it will look like IgnoreUnrelatedDimensions=True for this member and children, as you said before.

One more tip: if there is a filtering on another levels you can play with Descendants levels and flag parameter (AFTER, BEFORE, SELF_AND_AFTER etc.) This article can help to understand this technique: http://www.sqllion.com/2011/08/mdx-descendants/

Hope this will help to solve the problem.

UPDATE For the lowest level it's better to use Ascendants like this:

CREATE MEMBER CURRENTCUBE.[Measures].[TestAgg2]
 AS
    IIF(
    Intersect(
        [Create Date].[Create Date].CurrentMember,
        Exists(
            Ascendants([Create Date].[Create Date].[Day].&[20140214])
        )
    ).Count > 0,
    (
        [Create Date].[Create Date].[Day].&[20140214],
        [Measures].[Count]
    ),
    IIF(
        [Create Date].[Create Date].CurrentMember is [Create Date].[Create Date].[All],
        AGGREGATE(
            {[Create Date].[Create Date].[Day].&[20140214]},
            [Measures].[Count]
        ),
        null
    )
),
VISIBLE = 1;