As one of the tasks on hand, I had to figure out the backlog which by definition is a running total of all ‘open’ incidents at the end of a day. The dimension table being used has all the incident details and related attributes such as status, priority, category, etc 

So, I created a caculated measure, named [Incident Backlog Count], based on the [Incident Count] measure in the measure group, as: 

IIF ([date hierarchy].CurrentMember IS NULL,
 NULL,
 IIF(IsEmpty(Measures.[Incident Count]),
  IIF([date hierarchy].Parent.FirstChild IS [date hierarchy].CurrentMember,
   (Measures.[Incident Backlog Count], [date hierarchy].Parent.PrevMember),
   (Measures.[Incident Backlog Count], [date hierarchy].PrevMember)),
  IIF([date hierarchy].LEVEL IS [date hierarchy].[Date],
   (Measures.[Incident Count]),  IIF(IsEmpty((Measures.[Incident Backlog Count],[Date Hierarchy].LastChild)),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild.PrevMember),
   (Measures.[Incident Backlog Count],[Date Hierarchy].LastChild))
  )
 )
)
  

The recursion worked well and the MDX seemed to simulate the LastNonEmptyChild function, except that at the leaf level, all the values were getting populated – even for the empty members !   

For the life of me, I could not figure out the reason why this was happening, until I went in the Calculated Member Form Editor for the calculated measure and set the Additional Property Non Empty Behavior to the default measure [Incident Count] 

Here’s what Microsoft has to say about the Calculated Member Form Editor 

If the Non Empty Behavior property is blank, the calculated member must be evaluated repeatedly to determine if a member is empty. If the Non Empty Behavior property contains the name of a measure, the calculated member is treated as empty if the specified measure is empty. 

  

  

Advertisements