MDX calculated measure for dimension member

2
votes

I would like to create new measure called Efficiency that shows the value of non-ordered transactions to total value:

Efficiency=[100%-(nonordered/total)]

Unfortunately, my experience with MDX is rather short. I tried this code:

    CREATE MEMBER CURRENTCUBE.[Measures].[Efficiency] 
AS 
IIF([Measures].[Offers Value]=0, 0
, WITH MEMBER [Measures].[Offers Value] AS
1- ([Measures].[Offers value], [Is Ordered].[Is Ordered - Status].&[0])
/ ([Measures].[Offers Value]))
,FORMAT_STRING="PERCENT",
, VISIBLE = 1;

Problems encounterd are:

  1. [Offers value]=0 or other error during calculation. Is there some solution like 'OnError' or 'IsError' in MDX?
  2. [Offers value] is also a calculated measure. How can I decide to calculate [Offers value] prior to [Efficiency]?
1
visual-studiossasmdxolapdimension

1 Answers

2
votes

To answer your second question: You would create two measures, and if you create Offers value first and position Efficiency after it in the calculation script, then you can just use the first one in the second.

You cannot use WITH in the calculation script, but only in SELECT statements. That is probably the reason why you get an error message (your first question).

Any your measure definition should probably look something like

CREATE MEMBER CURRENTCUBE.[Measures].[Efficiency] 
AS 
IIF([Measures].[Offers Value]=0, 0
, 1 - ([Measures].[Offers value], [Is Ordered].[Is Ordered - Status].&[0])
  / ([Measures].[Offers Value]))
,FORMAT_STRING="PERCENT",
, VISIBLE = 1;

just leaving away the WITH ... AS. You were close to it!