title: MDX Query using Aggregate Filter Calculated Member too slow
body: <p>I am new to MDX. I am trying to run the following MDX Query in an SSRS report and it is taking ages to finish running it. Sometimes I have to cancel the Query because it is too slow.</p>
  
  <pre><code>WITH 
    --All forms ordered ASC
    SET [OrderedSet] AS 
      Order
      (
        NonEmpty
        (
          [Form].[Number].[Number]
         ,[Measures].[Sales]
        )
       ,[Measures].[Sales]
       ,BASC
      ) 
    --Rank the forms in [orderedSet]
    MEMBER [Measures].[Rank] AS 
      Rank
      (
        [Form].[Number].CurrentMember
       ,[OrderedSet]
      ) 
    --Running Total
    MEMBER [Measures].[Running Total] AS 
      Sum
      (
        Head
        (
          [OrderedSet]
         ,(
            [Measures].[Rank]
           ,[Form].[Number].CurrentMember
          )
        )
       ,[Measures].[Sales]
      ) 
    --Total Sales
    MEMBER [Measures].[Total Sales] AS 
      Sum
      (
        [OrderedSet]
       ,[Measures].[Sales]
      ) 
    --Find the spending band Sales limits
    MEMBER [Measures].[3%Sales] AS 
      0.03 * [Measures].[Total Sales] 
    MEMBER [Measures].[8%Sales] AS 
      0.08 * [Measures].[Total Sales] 
    MEMBER [Measures].[18%Sales] AS 
      0.18 * [Measures].[Total Sales] 
    MEMBER [Measures].[38%Sales] AS 
      0.38 * [Measures].[Total Sales] 
    MEMBER [Measures].[62%Sales] AS 
      0.62 * [Measures].[Total Sales] 
    MEMBER [Measures].[82%Sales] AS 
      0.82 * [Measures].[Total Sales] 
    MEMBER [Measures].[92%Sales] AS 
      0.92 * [Measures].[Total Sales] 
    MEMBER [Measures].[97%Sales] AS 
      0.97 * [Measures].[Total Sales] 
    --Assign Sales limits for each spending bands
    MEMBER [Measures].[MinSales] AS 
      CASE 
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[0-3] 
        THEN 1
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[3-8] 
        THEN 
          [Measures].[3%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[8-18] 
        THEN 
          [Measures].[8%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[18-38] 
        THEN 
          [Measures].[18%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[38-62] 
        THEN 
          [Measures].[38%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[62-82] 
        THEN 
          [Measures].[62%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[82-92] 
        THEN 
          [Measures].[82%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[92-97] 
        THEN 
          [Measures].[92%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[97-100] 
        THEN 
          [Measures].[97%Sales]
      END 
    MEMBER [Measures].[MaxSales] AS 
      CASE 
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[0-3] 
        THEN 
          [Measures].[3%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[3-8] 
        THEN 
          [Measures].[8%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[8-18] 
        THEN 
          [Measures].[18%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[18-38] 
        THEN 
          [Measures].[38%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[38-62] 
        THEN 
          [Measures].[62%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[62-82] 
        THEN 
          [Measures].[82%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[82-92] 
        THEN 
          [Measures].[92%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[92-97] 
        THEN 
          [Measures].[97%Sales]
        WHEN 
          [Form].[Number].CurrentMember = [Form].[Number].[97-100] 
        THEN 
          [Measures].[Total Sales]
      END 
    --Create Spending bands based on the Running Total ranges
    MEMBER [Form].[Number].[0-3] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; 0
          AND 
            [Measures].[Running Total] &lt;= [Measures].[3%Sales]
        )
      ) 
    MEMBER [Form].[Number].[3-8] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[3%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[8%Sales]
        )
      ) 
    MEMBER [Form].[Number].[8-18] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[8%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[18%Sales]
        )
      ) 
    MEMBER [Form].[Number].[18-38] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[18%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[38%Sales]
        )
      ) 
    MEMBER [Form].[Number].[38-62] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[38%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[62%Sales]
        )
      ) 
    MEMBER [Form].[Number].[62-82] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[62%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[82%Sales]
        )
      ) 
    MEMBER [Form].[Number].[82-92] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[82%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[92%Sales]
        )
      ) 
    MEMBER [Form].[Number].[92-97] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[92%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[97%Sales]
        )
      ) 
    MEMBER [Form].[Number].[97-100] AS 
      Aggregate
      (
        Filter
        (
          [OrderedSet]
         ,
            [Measures].[Running Total] &gt; [Measures].[97%Sales]
          AND 
            [Measures].[Running Total] &lt;= [Measures].[Total Sales]
        )
      ) 
  SELECT 
    NON EMPTY 
      {
        [Measures].[MinSales]
       ,[Measures].[MaxSales]
       ,[Measures].[Form Count]
       ,[Measures].[Sales]
      } ON 0
   ,{
      [Form].[Number].[0-3]
     ,[Form].[Number].[3-8]
     ,[Form].[Number].[8-18]
     ,[Form].[Number].[18-38]
     ,[Form].[Number].[38-62]
     ,[Form].[Number].[62-82]
     ,[Form].[Number].[82-92]
     ,[Form].[Number].[92-97]
     ,[Form].[Number].[97-100]
    } ON 1
  FROM 
  (
    SELECT 
      [blah] ON COLUMNS
    FROM [Cube]
  );
  </code></pre>
  
  <p>After few tests and google search, I know that the culprit is the calculated member that aggregates the filtered Form Numbers. Also the [Form].[Number].[Number] dimension is a huge dimension with hundreds of thousands of members. So was wondering if we create a calculated member in such a dimension, would that slow down the query and if so what can be a workaround?</p>
  
  <p>Many Thanks</p>