Aggregation of an MDX calculated measure when multiple time periods are selected

Question

In my SSAS cube, I've several measures defined in MDX which work fine except in one type of aggregation across time periods. Some don't aggregate (and aren't meant to) but one does aggregate but gives the wrong answers. I can see why, but not what to do to prevent it.

The total highlighted in the Excel screenshot below (damn, not allowed to include an image, reverting to old-fashion table) is the simplest case of what goes wrong. In that example, 23,621 is not the grand total of 5,713 and 6,837.

       Active Commitments    Acquisitions    Net Lost Commitments    Growth in Commitments
2009   88,526                13,185          5,713                   7,472
2010   92,125                10,436          6,837                   3,599
Total                        23,621          23,621 

1. Active Commitments works fine. It is calculated for a point in time and should not be aggregated across time periods.
2. Acquisitions works fine.
3. [Measures].[Growth in Commitments] = ([Measures].[Active Commitments],[Date Dimension].[Fiscal Year Hierarchy].currentMember) - ([Measures].[Active Commitments],[Date Dimension].[Fiscal Year Hierarchy].prevMember)
4. [Measures].[Net Lost Commitments] = ([Measures].[Acquisitions] - [Measures].[Growth in Commitments])

What's happening in the screenshot is that the total of Net Lost Commitments is calculated from the total of Acquisitions (23,621) minus the total of Growth in Commitments (which is null).

Aggregation of Net Lost Commitments makes sense and works for non-time dimensions. But I want it to show null when multiple time periods are selected rather than an erroneous value. Note that this is not the same as simply disabling all aggregation on the time dimension. The aggregation of Net Lost Commitment works fine up the time hierarchy -- the screenshot shows correct values for 2009 and 2010, and if you expand to quarters or months you still get correct values. It is only when multiple time periods are selected that the aggregation fails.

So my question is how to change the definition of Net Lost Commitments so that it does not aggregate when multiple time periods are selected, but continues to aggregate across all other dimensions? For instance, is there a way of writing in MDX:

CREATE MEMBER CURRENTCUBE.[Measures].[Net Lost Commitments]
 AS (iif([Date Dimension].[Fiscal Year Hierarchy].**MultipleMembersSelected**
        , null
        , [Measures].[Acquisitions] - [Measures].[Growth in Commitments]))

ADVthanksANCE,

Matt.

Answer 1

A suggestion from another source has solved this for me. I can use --

iif(iserror([Date Dimension].[Fiscal Year Hierarchy].CurrentMember), 
   , null
   , [Measures].[Acquisitions] - [Measures].[Growth in Commitments]))

CurrentMember will return an error when multiple members have been selected.

Answer 2

I didn't understand much of the first part of the question, sorry...but at the end I think you ask how to detect if multiple members from a particular dimension are in use in the MDX.

You can examine either of the two axes as a string, and use that to form a true/false test. Remember you can use VBA functions in Microsoft implementations of MDX.

I suggest InStr(1, SetToStr(StrToSet("Axis(1)")), "whatever") = 0 as a way to craft the first argument of your IIF.

This gets the set of members on axis number one, converts it to a string, and looks to see if a certain string is present (it returns the position of that string within the other). Zero means not found (so it returns true). You may need to use axis zero instead, or maybe check both.

To see if multiple members from the same dimension were used, the test string above would have to be more complicated. You want to know if whatever occurs once or twice. You could test if the first occurance of the string was at the same position as the last occurance (by searching backwards); though that could also mean the string wasn't found at all:

IIF(
   InStr(1, bigstring, littlestring) = InStrRev(bigstring, littlestring), 
   'used once', 
   'used twice or not at all'
)

Answer 3

I came across this post while researching a solution for my own issue with grand totals of calculated measures over time when filters are involved. I think you could have fixed the calculations instead of suppressing them by using dynamic sets. This worked for me.