MDX / Filter / Understanding Cube Values

[Steve Vincent]

I think i know the answer to this but i wanted to check in case i've missed something.

If i write some MDX to compare values between 2 months in a cube with 5 dimensions, what does the code use to evaluate the subset when some dimensions are not specified in the MDX? For example, i have a cube of headcount data and i want to write MDX on the employees dimension that looks for a change between 2 months. It might be department, site, resource type etc. So my code might look like;

Code: Select all

{FILTER (
  {TM1SUBSETALL( [Employees] )}
,  ([actuals].([MonthYear].[Feb - 2009], [Actuals Measures].[Actual Headcount]) = 1) AND
   ([actuals].([MonthYear].[Jan - 2009], [Actuals Measures].[Actual Headcount]) = 0)
)}

As it stands it picks up people brand new to the cube, but not those that have moved. It appears that any dimension i've not put in the code is being evaluated for ANY element within it. So if an employee has moved department, although you look at just one department in a view the MDX will not pick it up because they would be in the previous month under a different element.

So the type of filter i'm looking for appears to be impossible using MDX. Ideally i'd like the MDX to evaluate the selections the user has made in their cube view, and reselect its elements accordingly. I don't think i can do that, but does anyone know any different?