locked
Restricting/Setting a Measure based another Measure and dimensionality RRS feed

  • Question

  • Here's my caculated measure "Test"

    with member [Measures].[Test] as
    IIF(
      ([Measures].[MEDIATION SEGMENT COUNT],[STD ENTERPRISE DATE].[CAL DATE] ) > 0,1,NULL)
     
    select
    {[Measures].[Test],[Measures].[MEDIATION SEGMENT COUNT]} on 0,
    filter([STD ENTERPRISE DATE].[CAL DATE].members,[Measures].[Test]>0) on 1
    from [GIMLocal]

    I set the value to be 1 if the value of another measure is > 0, each day.   My problem is that the query that uses the measure ( select statement) does not behave the way I want.  The select statement works fine when I run the above query (by CAL DATE for rows).  However, if I substitute CAL YEAR MONTH in for CAL DATE the results return "1" if the value of my other measure ([Measures].[MEDIATION SEGMENT COUNT]) is > 0 for each month rather than summing up the 1's at the day level. 

    Here's results that I like that give me a "1" by date (the ALL level is incorrect though):

    (presented as date, test, MEDIATION SEGMENT COUNT]
    All, 1, 287
    2008-10-23 00:00:00, 1, 4
    2008-10-28 00:00:00,1, 1
    2008-10-29 00:00:00, 1, 35
    2008-10-30 00:00:00, 1, 33
    2008-10-31 00:00:00, 1, 22
    2008-11-11 00:00:00, 1, 15
    2008-11-17 00:00:00, 1, 20
    2008-11-18 00:00:00, 1, 26
    2008-11-19 00:00:00, 1, 32
    2008-11-21 00:00:00, 1, 38
    2008-11-24 00:00:00, 1, 25
    2008-11-25 00:00:00, 1, 26
    2008-12-24 00:00:00, 1, 10

    Here's the query using the CAL YEAR MONTH that do not sum properly.  For example, 2008Oct should have a value of 5 for the Test measure.
     
    presented as DATE YEAR MONTH, Test, MEDIATION SEGMENT COUNT
    All, 1, 287
    2008Dec, 1, 10
    2008Nov, 1, 182
    2008Oct, 1, 95

    How do a create a measure that would sum properly?

    ...
    Here's the query with the Month level substituted in:
    with member [Measures].[Test] as
    IIF(
      ([Measures].[MEDIATION SEGMENT COUNT],[STD ENTERPRISE DATE].[CAL DATE] ) > 0,1,NULL)
     
    select
    {[Measures].[Test],[Measures].[MEDIATION SEGMENT COUNT]} on 0,
    filter([STD ENTERPRISE DATE].[CAL YEAR MONTH].members,[Measures].[Test]>0) on 1
    from [GIMLocal]




    Tim Webber
    Friday, March 27, 2009 7:19 PM

Answers

  • You can see the answer in your data, at the all level the MEDIATION SEGMENT COUNT is > 1, so the calculated measure is returning 1. When you query at the month level the CAL DATE attribute will return the [All] member. What you need to do is to sum the dates that exist in relation to the currently selected month. Something along the lines of the following might work (my changes are bolded)


    with member [Measures].[Test] as
    SUM(
     IIF(
      ([Measures].[MEDIATION SEGMENT COUNT],EXISTING [STD ENTERPRISE DATE].[CAL DATE] ) > 0,1,NULL)
    )
     
    select
    {[Measures].[Test],[Measures].[MEDIATION SEGMENT COUNT]} on 0,
    filter([STD ENTERPRISE DATE].[CAL YEAR MONTH].members,[Measures].[Test]>0) on 1
    from [GIMLocal]
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Monday, March 30, 2009 1:39 AM