Aggregating the calculated MDX measure when selecting multiple time periods
In my SSAS cube, I have several measures defined in MDX that work great except for one type of aggregation over time periods. Some are not aggregated (and not intended), but one does aggregate but gives the wrong answers. I can understand why, but not what needs to be done to prevent it.
The total highlighted in the Excel screenshot below (heck, not allowed to include an image, going back to the old fashion table) is the simplest case of what goes wrong. In this example, 23,621 is not the 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
- Active actions work fine. It is calculated at a specific point in time and does not need to be aggregated over periods of time.
- Acquisitions work great.
- [Measures]. [Growth in Liabilities] = ([Measures]. [Active Liabilities], [Date Size]. [Fiscal Year Hierarchy]. Current Member) - ([Measures]. [Active Liabilities], [Date Size]. [Fiscal Year Hierarchy ] .prevMember)
- [Measures]. [Net Liabilities Lost] = ([Measures]. [Acquisitions] - [Measures]. [Growth in Liabilities])
What happens in the screenshot is that the total Net Lost Commitments are calculated from the total acquisitions (23,621) minus the total growth in commitments (which is zero).
Net Lost Commitments aggregation makes sense and works for non-urgent measurements. But I want it to display null when multiple time periods are selected, not an erroneous value. Note that this is not the same as simply turning off all time-based aggregations. Net Lost Commitment aggregation improves the time hierarchy - the screenshot displays the correct values for 2009 and 2010, and if you expand to quarters or months, you still get the correct values. Only when multiple time periods are selected does the aggregation fail.
So my question is, how do I change the definition of Net Lost Commitments so that it doesn't aggregate across multiple time periods, but keeps aggregating across all other dimensions? For example, there is a way to write to MDX:
CREATE MEMBER CURRENTCUBE.[Measures].[Net Lost Commitments]
AS (iif([Date Dimension].[Fiscal Year Hierarchy].**MultipleMembersSelected**
, null
, [Measures].[Acquisitions] - [Measures].[Growth in Commitments]))
ADVthanksANCE,
Mt.
source to share
I didn't know much about the first part of the question, sorry ... but at the end I think you are asking how to determine if multiple members from a specific dimension are used in MDX.
You can test either of the two axes as a string and use that to generate a true / false test. Remember, you can use VBA functions in Microsoft's MDX implementations.
I suggest InStr(1, SetToStr(StrToSet("Axis(1)")), "whatever") = 0
how to create the first argument of your IIF
.
This gets the set of elements on axis number one, converts it to a string, and see if any row is present (it returns the position of that row in another). Zero means not found (which is why it returns true). You may need to use axis zero, or perhaps check both.
To see if multiple items from the same dimension were used, the above test string should be more complex. You want to know if it happens whatever
once or twice. You can check if the first occurrence of the line was in the same position as the last event (by searching backward); although this could also mean the line was not found at all:
IIF(
InStr(1, bigstring, littlestring) = InStrRev(bigstring, littlestring),
'used once',
'used twice or not at all'
)
source to share
I came across this post while researching a solution for my own problem with grandiose totals of calculated measures over time when filters are involved. I think you could fix the computation instead of suppressing it using dynamic sets. This worked for me .
source to share