Hello,

I have a need for a dimension's members to aggregate differently based
on the levels of two other dimensions. Details below:

3 Dimensions:
TIME
[YEAR] -> [QUARTER] -> [MONTH] -> [WEEK]

PRODUCT
[CATEGORY] -> [SUBCATEGORY] -> [4 DIGIT] -> [6 DIGIT] -> [ITEM]

VARIABLES
[VARIABLE PARENT] -> [VARIABLE] (NON AGGREGATED)


In the Variables dimension, I have a Member called [I_WEEKS_FORWARD_U]
whose value is fed from a fact table. What I need to do is the
following:

1. For the variable [I_WEEKS_FORWARD_U] at every level in the TIME
dimension except [WEEK], take the value of the last child of the
current TIME member.
2. For the variable [I_WEEKS_FORWARD_U] at every level in the PRODUCT
dimension except [ITEM], take an Average of all the
([ITEM],[I_WEEKS_FORWARD_U]) values that are descendants of the
current PRODUCT member.

the way I am trying to accomplish this is by having Custom Rollup
formulas at each level (except the lowest) for the TIME and PRODUCT
dimensions. The formulas are as follows:
PRODUCT

IIF([VARIABLES].CURRENTMEMBER IS [VARIABLES].[I_WEEKS_FORWARD_U],
AVG(DESCENDANTS([PRODUCT].CURRENTMEMBER,[ITEM]),[VARIABLES].[I_WEEKS_FORWARD_U]),
[VARIABLES].CURRENTMEMBER)

TIME

IIF([VARIABLES].CURRENTMEMBER IS [VARIABLES].[I_WEEKS_FORWARD_U],
([TIME].CURRENTMEMBER.LASTCHILD,[VARIABLES].[I_WEEKS_FORWARD_U]),
[VARIABLES].CURRENTMEMBER)

Problem 1: Both of these work fine if the only VARIABLES dimension
member I have in my query is [I_WEEKS_FORWARD_U]. If I use any other
VARIABLES member with TIME or PRODUCT, I get an Infinite Recursion
error.

Problem 2: Assuming I get these formulas to work eventually, is this
the proper approach to this type of problem? I am concerned with
performance, because I will now be evaluating an MDX expression on the
fly whenever TIME or PRODUCT is used (99% of queries). Are there any
other options to do this? I thought about making the
[I_WEEKS_FORWARD_U] member a custom member that is "dimensionally
aware" (i.e. knows what dimensions it is being evaluated against), and
this works fine for custom aggregation along a single dimension, but I
am not sure how you would do this for intersections of PRODUCT and
TIME.

Any help provided would be most welcome. Thanks,

- Marc