MDX calculated measure based on dimension 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 ....,


   CASE 
         WHEN a.[Action]='added' 

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


 AS 'Lifespan'

 FROM FilterData a 
 GROUP BY a.[Target]

Question: How can I create a caculated measure for lifespan ?

I have tried following but failed, [Dim Action Types].[Name].CURRENTMEMBER always is "ALL" ;

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]

I can get some data use query :

WITH MEMBER [Measures].[TimeSpan] as 
case
 WHEN [Action Types].[Name].CURRENTMEMBER IS [Action Types].[Name].&[added]       THEN datediff('D',[Date].[Date].CURRENTMEMBER.NAME,Now())
WHEN [Action Types].[Name].CURRENTMEMBER IS  [Action Types].[Name].&  [removed] THEN -datediff('D',[Date].[Date].CURRENTMEMBER.NAME,Now())
ELSE  0
END

SELECT [Measures].[TimeSpan] ON 0,
NONEMPTY([Date].[Date].Children*[Action Types].[NAME].Children)on 1
FROM [OLAP Prep]

To get the output like this

                           TimeSpan
2014-07-23 00:00:00.000 added
  34
2014-07-23 00:00:00.000 connected
     0
2014-07-23 00:00:00.000 disconnected
     0
2014-07-23 00:00:00.000 removed
     -34
2014-07-25 00:00:00.000 added
  32
2014-07-25 00:00:00.000 connected
0
2014-07-25 00:00:00.000 disconnected
 0
 2014-08-04 00:00:00.000 added
   22

But it won't help much , I want this timespan value as a measure like "Amount" for each database rows, then I can do sum ... it's hard to explain. it is supposed to be a column in fact datatable named "timespan" (measure) , but it is calculated by today's date, can't created in datatable, later on I created a named calculated column in dsv, using GETDATE(). But the GETDATE() is the processing date, so I have to create a calculated measure here...

The query I used in t-sql :

 SELECT DATENAME(DAY,a.[Date])+'/'+ DATENAME(MONTH,a.[Date]) AS 'X',
 SUM(CASE WHEN a.[Action]='added' THEN DATEDIFF(DAY, a.[Date],GETDATE()) 
 ELSE                           DATEDIFF(DAY, a.[Date],GETDATE())*-1 END)
 AS 'TimeSpan',
 FROM FactData a
 GROUP BY a.[Date]

Daniel Hanczyc Daniel Hanczyc · Accepted Answer · 2014-08-26T09:22:03

[Dim Action Types] also need to be on rows as currently there is no crossing on [Dim Action Types] * [Dim Action Targets] - let me know if it works.

MDX calculated measure based on dimension value

1 Answers