MDX calculated measure on dimension value without measure value

Question

I am new to mdx and SSAS, We are trying to convert our store procedure to mdx query. I have a cube like below:

Fact table：

         [Target] (FK),
         [Date] (FK),
         [Action] (FK),
         [Amount] (Measure),

Dim Date:

        [DateKey] (pk),
        [Date],
        [DayOfYear],
        [DayofWeek],
        [....]

Dim Action: has fourkind of actions (added, removed, connected, ..)

        [ID] (PK)
        [Name]

Dim Target:

        [ID] (PK)
        [Name]

I want to create a calculated measure like what we did in t-sql

 SELECT ....,
 SUM(

   CASE 
         WHEN a.[Action]='added' 

         THEN DATEDIFF(DAY, a.[Date],GETUTCDATE()) 
         ELSE DATEDIFF(DAY, a.[Date],GETUTCDATE())*-1
         END

)/NULLIF( SUM(CASE WHEN a.[Action]='added' THEN 1 ELSE 0 END),0)
 AS 'AverageLifespan'
 FROM FilterData a 
 GROUP BY a.[Target]

I have tried following but failed, it only returns 0 ;

With Member [Measures].[LifeSpan] as
CASE  
WHEN  [Dim  Action Types].[Name].CURRENTMEMBER IS [Dim  Action Types].[Name].&[added] THEN  1*'datediff("d",[Dim Date].[Date],Now()) '
WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS  [Dim  Action Types].[Name].&[removed] THEN -1*'datediff("d",[Dim Date].[Date],Now()) '
ELSE  0
End 
select [Measures].[LifeSpan] on columns,
[Dim Action Targets].[Name].Children on rows
from [OLAP Prep]

-----------output---------------

                         LifeSpan

tilerecipes/account-news    0
tilerecipes/competitornews  0
tilerecipes/innovation-blog 0
tilerecipes/sales-now       0
tilerecipes/inbox           0
tilerecipes/in-the-news     0
tilerecipes/jobs            0
tilerecipes/l-and-d         0

Tried on below query, still not working (removed the date part, just for testing):

 With Member [Measures].[LifeSpan] as
 CASE  
 WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS [Dim  Action Types].                [Name].&[added] THEN  1 
 WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS  [Dim  Action Types].  [Name].&[removed] THEN -1
 WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS  [Dim  Action Types].[Name].[All] THEN Sum([Dim  Action Types].[Name].Children,[Measures].[LifeSpan])   -- this will work.
 ELSE  0
 End

 select [Measures].[LifeSpan] on columns,
 [Dim  Action Types].[Name].Members on rows
 from [OLAP Prep]

  --------Output------------

                  LifeSpan
 All                   0
added                  1
connected              0
disconnected           0
removed               -1

Finally fixed it by adding a named calculation column in dsv, then use that column as measure — ewan

Johan Buret Johan Buret · Accepted Answer · 2014-08-19T08:27:34

There is one thing to remember in MDX : Calculated measures are not aggregated.

When you develop along the [Dim Action Targets].[Name] Axis, the [Dim Action Targets].[Name] default member is used, which is by default [Dim Action Targets].[Name].[All]

If you tried

select [Measures].[LifeSpan] on columns,
[Dim Action Types].[Name].Members on rows
from [OLAP Prep]

You will see your calculated measures behave properly, except for the [Dim Action Types].[Name].[All] Member.

In order for your calculated measure to behave properly on your original request

With Member [Measures].[LifeSpan] as
CASE  
WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS [Dim  Action Types].[Name].&[added] THEN  1*'datediff("d",[Dim Date].[Date],Now()) '
WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS  [Dim  Action Types].[Name].&[removed] THEN -1*'datediff("d",[Dim Date].[Date],Now()) '
WHEN [Dim  Action Types].[Name].CURRENTMEMBER IS  [Dim  Action Types].[Name].[All] THEN Sum([Dim  Action Types].[Name].Children,[Measures].[LifeSpan]) -- this will work.
ELSE  0
End

Well, the simplest way would be to create a LifeSpan measure in your cube definition, make it point to your Amount column of your fact table. Let the default behaviour

Now, go to the Calculation tab of the cube definition. Switch to Script mode.

You should see the "CALCULATE" instruction.

CALCULATE;

Scope([Measures].[LifeSpan]);
    This = 0; // We zero out the whole thing first
    Scope([Dim Date].[Date].Children,[Dim  Action Types].[Name].&[added]})
        This =  datediff("d",[Dim Date].[Date].CURRENTMEMBER,Now())
    End Scope;
    Scope([Dim Date].[Date].Children,[Dim  Action Types].[Name].&[removed]})
        This =  -datediff("d",[Dim Date].[Date].CURRENTMEMBER,Now())
    End Scope;
End Scope;

This measure will aggregate the right way.

MDX calculated measure on dimension value without measure value

2 Answers