CODEWITHSUNDEEP
mdxmsas
laxmi
laxmi

Reputation: 13

MDX query with nested aggregations

WITH 
  MEMBER [Measures].[newCalculatedMeasure] AS 
    Sum
    (
      Filter
      (
        Descendants
        (
          [Date].[28 Days Month Calendar].CurrentMember
         ,2
         ,LEAVES
        )
       ,
          [Measures].[mymeasure] * 10
        > 
          Avg
          (
            Filter
            (
              Descendants
              (
                [Date].[28 Days Month Calendar].CurrentMember
               ,2
               ,LEAVES
              )
             ,
              [Measures].[mymeasure] > 0
            )
           ,[Measures].[mymeasure]
          )
      )
     ,[Measures].[mymeasure]
    ) 
SELECT 
  [Date].[28 Days Month Calendar].MEMBERS ON 0
 ,[Measures].[newCalculatedMeasure] ON 1
FROM [cube];

Above query is not evaluating the inside average function, whereas if i replace that with the actual average, its working fine

Can somebody tell me whats wrong with the above measure..

Upvotes: 1

Views: 344

Answers (1)

whytheq
whytheq

Reputation: 35557

Does splitting the measure into named sets help with this context issue?

WITH 
  SET [MYSET] AS 
    Descendants
    (
      [Date].[28 Days Month Calendar].CurrentMember
     ,2
     ,LEAVES
    ) 
  SET [MYSETFILTERED] AS 
    Filter
    (
      [MYSET]
     ,
      [Measures].[mymeasure] > 0
    ) 
  MEMBER [Measures].[newCalculatedMeasure] AS 
    Sum
    (
      Filter
      (
        [MYSET]
       ,
        [Measures].[mymeasure] * 10 > Avg([MYSETFILTERED],[Measures].[mymeasure])
      )
     ,[Measures].[mymeasure]
    ) 
SELECT 
  [Date].[28 Days Month Calendar].MEMBERS  ON 0
 ,[Measures].[newCalculatedMeasure] ON 1
FROM [cube];

Named sets with aggregates can sometimes impact performance so please excuse me if the above slows everything up.

Upvotes: 1

Related Questions