MDX calculated measure on dimension value without measure value -

i new mdx , ssas, trying convert our store procedure mdx query. have cube 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 create calculated measure did in t-sql

 select ....,  sum(     case           when a.[action]='added'            datediff(day, a.[date],getutcdate())           else datediff(day, a.[date],getutcdate())*-1          end  )/nullif( sum(case when a.[action]='added' 1 else 0 end),0)  'averagelifespan'  filterdata   group a.[target] 

i have tried following failed, returns 0 ;

with member [measures].[lifespan] case   when  [dim  action types].[name].currentmember [dim  action types].[name].&[added]  1*'datediff("d",[dim date].[date],now()) ' when [dim  action types].[name].currentmember  [dim  action types].[name].&[removed] -1*'datediff("d",[dim date].[date],now()) ' else  0 end  select [measures].[lifespan] on columns, [dim action targets].[name].children on rows [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 date part, testing):

 member [measures].[lifespan]  case    when [dim  action types].[name].currentmember [dim  action types].                [name].&[added]  1   when [dim  action types].[name].currentmember  [dim  action types].  [name].&[removed] -1  when [dim  action types].[name].currentmember  [dim  action types].[name].[all] sum([dim  action types].[name].children,[measures].[lifespan])   -- work.  else  0  end   select [measures].[lifespan] on columns,  [dim  action types].[name].members on rows  [olap prep]    --------output------------                    lifespan                    0 added                  1 connected              0 disconnected           0 removed               -1 

there 1 thing remember in mdx : calculated measures not aggregated.

when develop along [dim action targets].[name] axis, [dim action targets].[name] default member used, default [dim action targets].[name].[all]

if tried

select [measures].[lifespan] on columns, [dim action types].[name].members on rows [olap prep] 

you see calculated measures behave properly, except [dim action types].[name].[all] member.

in order calculated measure behave on original request

with member [measures].[lifespan] case   when [dim  action types].[name].currentmember [dim  action types].[name].&[added]  1*'datediff("d",[dim date].[date],now()) ' when [dim  action types].[name].currentmember  [dim  action types].[name].&[removed] -1*'datediff("d",[dim date].[date],now()) ' when [dim  action types].[name].currentmember  [dim  action types].[name].[all] sum([dim  action types].[name].children,[measures].[lifespan]) -- work. else  0 end 

---

well, simplest way create lifespan measure in cube definition, make point amount column of fact table. let default behaviour

now, go calculation tab of cube definition. switch script mode.

you should see "calculate" instruction.

calculate;  scope([measures].[lifespan]);     = 0; // 0 out whole thing first     scope([dim date].[date].children,[dim  action types].[name].&[added]})         =  datediff("d",[dim date].[date].currentmember,now())     end scope;     scope([dim date].[date].children,[dim  action types].[name].&[removed]})         =  -datediff("d",[dim date].[date].currentmember,now())     end scope; end scope; 

this measure aggregate right way.