title: A different twist on the aggregation of calculated measures in SSAS
body: <p>I have to count something in a strange way, and it works in most cases -- both at leaves and at higher levels of aggregation across multiple dimensions. But it doesn't give correctly aggregated values for one specific dimension.</p>
  
  <p>What I have at the moment is ...</p>
  
  <pre><code>CREATE MEMBER CURRENTCUBE.[Measures].[Active Commitments]
   AS NULL,
  FORMAT_STRING = '#,#', 
  VISIBLE = 1;    
  
  SCOPE (DESCENDANTS([Date Dimension].[Fiscal Year Hierarchy],,AFTER));     
  [Measures].[Active Commitments] =
  iif([Constituent Activity].[Type].currentMember.Properties("Name")="Correspondence",
      sum(([Commitment Dates].[Start Date], NULL: [Date Dimension].[Fiscal Year Hierarchy]), [Measures].[Commitment Count]),
      sum(([Commitment Dates].[First Funded Date], NULL: [Date Dimension].[Fiscal Year Hierarchy]), [Measures].[Commitment Count]))
          - sum(([Commitment Dates].[Stop Date],[Commitment].[Was Commitment Ever Active].[Sometime Active], NULL: [Date Dimension].[Fiscal Year Hierarchy]),  [Measures].[Commitment Count]); 
  END SCOPE;
  
  SCOPE (DESCENDANTS([Date Dimension].[Fiscal Year Hierarchy],,AFTER));
  &lt;Similar to above&gt; 
  </code></pre>
  
  <p>As you can see, the complexity is that one type of "commitment" commences at the <code>[Start Date]</code> and others commence at the <code>[First Funded Date]</code>.</p>
  
  <p>This fails whenever multiple members of <code>[Constituent Activity]</code> are selected because in such cases the use of <code>currentMember</code> in the SCOPE statement is invalid. For example, the following MDX executes successfully but outputs <code>#Error</code> --</p>
  
  <pre><code>select  
      [Measures].[Active Commitments] on columns
      ,[Date Dimension].[Fiscal Year Hierarchy].[Fiscal Year].&amp;[2011\12] on rows
  from Compass3
  where
      {[Constituent Activity].[Description].[XYZ]
      ,[Constituent Activity].[Description].[ABC]}
  </code></pre>
  
  <p>I think what I need to encode within the SCOPE statement is the recursive ...</p>
  
  <pre><code>if a single member of [Constituent Activity] is current
  then use the calc as defined above
  else use [Measures].[Active Commitments] = sum(all selected members of [Constituent Activity], [Measures].[Active Commitments])
  </code></pre>
  
  <p>... but how would I write that?</p>