How to define a semi-additive MDX measure in cubing services (conditional distinct and then average based on date dimension)

Check list of all posts

Business requirement

Get number of distinct FALLOUT_ACCOUNT_KEY only if END_FALLOUT_DATE_KEY = 0 for each day, and then do the average based on it


Implemented in Cube services:

1)      Add calculated measure in the model as [Distinct Opened Fallout count 0]

Expression:

DISTINCT

(

CASE

 WHEN   @Column(F_FALLOUT.START_FALLOUT_DATE_KEY) = 0 THEN  @Column(F_FALLOUT.FALLOUT_ACCOUNT_KEY)

 ELSE (NULL)

 END

)

Aggregation: COUNT

2)      Add MDX calculated measure in the model as

Expression: AVG([Report Date].[Day].MEMBERS,[Measures].[Distinct Opened Fallout count 0])

3)      Add these two measures in cube

 
Implemented in Cognos DMR model:

Step 1: define Distinct Opened Fallout count 0 as

count(distinct

if ([Database View].[F_RECONCILIATION_FALLOUT].[END_FALLOUT_DATE_KEY]  =0)

   then ([Database View].[F_RECONCILIATION_FALLOUT].[FALLOUT_ACCOUNT_KEY]  )

else (null)

)

Step 2:



Another tip  to address MDX measure definition issue
In design studio, when adding a simple MDX Calculated Measure, such as define Current Difference measure as [Measures].[MeasueA] - [Measures].[MeasureB],  there is no validation problem. However, you can’t deploy cube.  When bowering members, there is error as
{

Some validation errors are detected.

Encountered a parsing error in "[Measures].Current Difference".: Not implemented

}
The solution is to make all the MDX calculated measure with [] as [Current Difference]