none
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.


    • Edited by GShenanigan Thursday, November 15, 2012 2:14 PM changed to reflect latest code
    Thursday, November 15, 2012 10:30 AM

Answers

  • The approach you've used is the exact approach I'd use. What errors are you getting? To test, I'd try defining your Row axis to a specific member just as a test. Also, in your StrToMember expressions, try adding the CONSTRAINED flag just to be sure that you've defined the member expression correctly.

    HTH, Martin


    http://martinmason.wordpress.com

    • Marked as answer by GShenanigan Thursday, November 15, 2012 3:08 PM
    Thursday, November 15, 2012 2:10 PM

All replies

  • The approach you've used is the exact approach I'd use. What errors are you getting? To test, I'd try defining your Row axis to a specific member just as a test. Also, in your StrToMember expressions, try adding the CONSTRAINED flag just to be sure that you've defined the member expression correctly.

    HTH, Martin


    http://martinmason.wordpress.com

    • Marked as answer by GShenanigan Thursday, November 15, 2012 3:08 PM
    Thursday, November 15, 2012 2:10 PM
  • Thanks for the reply.  I was having an issue with the FORMAT_STRING command, but I've sorted that and updated my MDX above to reflect that.  I don't get any error since sorting the format, just an empty column in my results (checking via SSMS it shows (null)).  I think I'm close to a solution, just needing that final piece to fall into place.

    EDIT: Thanks to your hint about the CONSTRAINED flag I found the error.  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.

    Thanks for your help Martin, it led me right to the issue.

    EDIT 2: The cause of the mismatch is not in the dimension, but in the format string I've used in theStrToMember 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"
    • Edited by GShenanigan Friday, November 16, 2012 11:50 AM Added new format string that resolves the issue
    Thursday, November 15, 2012 2:17 PM