How can I create a calculated measure based on a filtered dimension?

Question

I'm relatively new to MDX (in the process of ordering a book just now) and having a crack at creating a calculated measure, which should sum a value from one of my Fact tables, based on a set range of dates (from my Time dimension) which should be filtered by an attribute from another dimension. Unfortunately, I seem to have gotten myself in a bit of a situation and can't see a clear way out. I looked through other similar questions but didn't see any trying to do this same thing, only single dimension measures.

I have a Measure Group, which contains the physical measure SourceMeasure.

I have a Time dimension, named [Time], which I want to use to specify my range for the calculation. This is related to the above measure group by my Date Key.

I have an Account dimension, named [Dim Account], which contains two date attributes: [Account Start Date] and [Account End Date]. This is related to the above measure group via an Account ID.

What I'm trying to do, is filter the [Time] dimension to a range based on the [Account Start Date] and [Account End Date], and return the sum of [SourceMeasure] for the period specified for that account.

Obviously, this will be different for each account, so shouldn't be aggregated, except by the [Dim Account] dimension (perhaps returning 0 or null if not applied).

Example test expression below, this is currently not working due to an error with the FORMAT_STRING syntax (according to MDX Studio).

WITH 
  MEMBER [Measures].[LifetimeMeasure] AS 
    Sum
    (
      {
          StrToMember
          (
        "[Time].[Date].&[" 
            + Format([Dim Account].[Account Start Date].CurrentMember.MemberValue,"yyyy-MM-ddThh:mm:ss")
            + "]"
          )
        : 
          StrToMember
          (
            "[Time].[Date].&[" 
            + Format([Dim Account].[Account End Date].CurrentMember.MemberValue,"yyyy-MM-ddThh:mm:ss")
            + "]"
          )
      }
     ,[Measures].[SourceMeasure]
    ) 
SELECT 
{
    [Measures].[LifetimeMeasure]
    ,[Measures].[SourceMeasure]
}
ON COLUMNS
,{
    [Dim Account].[Account].[AccountName]
} 
ON ROWS
FROM 
    [MyCube]

I've tried a few different approaches (SCOPE, Filter, IIF) but every time I seem to get back to the same sticking point - how to filter the [Time] dimension based on the value of the [Dim Account] start and end dates.

Now, it may be that I'm completely misunderstanding how the relationships work between the dimensions and/or how calculated measures are computed by SSAS but I'm not yet at a level where I'm sure what to look for, so I'm going round in circles.

Is what I'm trying to do possible, and if so, any pointers as to where to look to help me figure out where I'm going wrong? Should I perhaps be looking to use a Calculation Dimension instead?

I hope this all makes sense, let me know if I've missed anything or if more detail is required. I'm testing using MDX Studio.

GShenanigan GShenanigan · Accepted Answer · 2012-11-15T15:10:19

I found the error by adding the CONSTRAINED flag to the StrToMember function.

It seems that my [Time] dimension's date entries have defaulted to the time "00:00:00" while my [Dim Account] dimension's date entries have defaulted to "12:00:00". It looks like a mismatch between 24-hour and 12-hour clock. My date fields on the [Dim Account] dimension are marked as "Regular", not "Date" due to the fact that the dimension isn't a natural time dimension. I think it's potentially this that's causing the mismatch.

To workaround the issue for now, I've removed the Time format from my Format expression and just appended the "00:00:00" to the string while I search for the root cause. I'll update once I've solved it.

UPDATE: The cause of the mismatch is not in the dimension, but in the format string I've used in the StrToMember expression. It's not documented on MSDN, but the following string outputs 12-hour time format:

"yyyy-mm-ddThh:mm:ss"

While this string outputs 24-hour time format:

"yyyy-mm-ddTHH:mm:ss"

How can I create a calculated measure based on a filtered dimension?

1 Answers