Measure lacking context

Question

I am struggling to force the measure CountTopPromos to be contextual.
Currently it is calculating a count of all the tuples in TopX. Against the version of AdWrks that I am running this is 14.

How do I force a context, of the current year, onto the measure CountTopPromos

WITH 
  SET FullSet AS 
    NonEmpty
    (
        [Date].[Calendar].[Calendar Year].MEMBERS
      * 
        [Promotion].[Promotion].MEMBERS
     ,[Measures].[Reseller Order Count]
    ) 
  SET TopX AS 
    Generate
    (
      [Date].[Calendar].[Calendar Year].MEMBERS
     ,TopPercent
      (
          [Date].[Calendar].CurrentMember
        * 
          Extract
          (
            FullSet
           ,[Promotion].[Promotion]
          )
       ,90
       ,[Measures].[Reseller Order Count]
      )
    ) 
  MEMBER [Measures].[CountTopPromos] AS 
    Count(TopX)                  //<<<<<<<<<<<< CONTEXT LACKING HERE  <<<<<<<
SELECT 
  NON EMPTY 
    {[Measures].[CountTopPromos]} ON 0
 ,{TopX} ON 1
FROM [Adventure Works];

Currently it returns the following:

enter image description here

I'd like CountTopPromos to be in the context of the year so the desired output is as follows:

enter image description here

What is your requirement? I understand you want to show "TopX across all time" on the rows. But what should the measure be? I assume you do NOT want to include row context, as then the count would always be 1.to s so that , but use a measure that hows how many of — FrankPl, Nov 14 '14 at 14:30
@FrankPl agreed OP was a little vague. I've edited and hopefully more precise now. — whytheq, Nov 14 '14 at 15:02

score 1 · Accepted Answer · edited May 23 '17 at 11:57

As sets are evaluated before the axes are defined (see my answer to this question), you cannot get row context into the calculation if you use the named set TopX. You could, however, copy its definition into the definition of [Measures].[CountTopPromos], replacing [Date].[Calendar].[Calendar Year].MEMBERS with [Date].[Calendar].CurrentMember:

  MEMBER [Measures].[CountTopPromos] AS 
    Count(    
        Generate
        (
          [Date].[Calendar].currentMember
         ,TopPercent
          (
              [Date].[Calendar].CurrentMember
            * 
              Extract
              (
                FullSet
               ,[Promotion].[Promotion]
              )
           ,90
           ,[Measures].[Reseller Order Count]
          )
        ) 
    )

delivers the result you want. And - as the Generate would loop over just one member here - you could omit that as well:

  MEMBER [Measures].[CountTopPromos] AS 
    Count(    
         TopPercent
          (
              [Date].[Calendar].CurrentMember
            * 
              Extract
              (
                FullSet
               ,[Promotion].[Promotion]
              )
           ,90
           ,[Measures].[Reseller Order Count]
          )
        )

and will get the same result.

+1 thanks frank. Yep I need to keep in mind conceptual execution order. Just thought of another possible solution that is quite elegant - have posted below - what do you think? — whytheq, Nov 14 '14 at 22:44

score 0 · Answer 2 · answered Nov 14 '14 at 22:42

0

Another alternative that is quite pretty:

MEMBER [Measures].[CountTopPromos] AS 
  Count
  (
    Intersect
    (
      {
        [Date].[Calendar].CurrentMember * [Promotion].[Promotion].MEMBERS
      }
     ,TopX
    )
  )

answered Nov 14 '14 at 22:42

whytheq

34,466
65
172
267

Measure lacking context

2 Answers2