How to avoid "Too many members created" message. Advanced formula with a variable

[timsmyth]

Hi

I have an issue with a fairly simple requirement for Advanced Script in a Data Action.

 I am comparing a Dimension COST_TYPE member SUPER, measure amount, for each Dimension POSITION, to COST_TYPE, member CON_CAP, measure amount, for POSITION unassigned.

If SUPER for a POSITION is greater than the CON_CAP/12 value, then post CON/CAP/12

There are many other dimensions in the model such as Employee, Cost Center, Pay Grade etc.

The gap in my understanding is this, for the "greater than" statement to work on each POSITION I thought I would need to include each dimension equal to unassigned in this part of the script, in bold below. 

RESULTLOOKUP([d/Measures]="Amount",[d/COST_TYPE]=#CAP)

if I do that, I get the message “Too many members created by PAY_RANGE, EMPLOYEE, CostCenter

So, in short how do I get records, where most of the dimensions are unassigned, to interact with records where all dimensions are assigned without the "Too many members message"?

Below is the full script

MEMBERSET [d/Date] = [d/Version].[p/StartPeriod] TO [d/Version].[p/EndPeriod]

MEMBERSET [d/Audit] = "MANUAL"

VARIABLEMEMBER #CAP OF [d/COST_TYPE]

DATA([d/Measures]="Amount",[d/COST_TYPE]= #CAP)=RESULTLOOKUP([d/Measures]="Rate_Absolute",[d/COST_TYPE]="CON_CAP”)/12                                                                                                                                                                           

IF

RESULTLOOKUP([d/Measures] = "Amount", [d/COST_TYPE] = "SUPER")>

RESULTLOOKUP([d/Measures]="Amount",[d/COST_TYPE]=#CAP)

THEN

DATA ([d/Measures] = "Amount", [d/COST_TYPE] = "SUPER")=RESULTLOOKUP([d/Measures]="Amount",[d/COST_TYPE]=#CAP)             

ELSE 

 ENDIF

Thanks

Tim

[Susanne_Helbig]

Hi @timsmyth,

if you know that a dimension is always equal unassigned, then you can add this dimension to your memberset definition:

MEMBERSET [d/Date] = [d/Version].[p/StartPeriod] TO [d/Version].[p/EndPeriod]
MEMBERSET [d/Audit] = "MANUAL"
MEMBERSET [d/Dimension_1] = "#"
MEMBERSET [d/Dimension_2] = "#"
MEMBERSET [d/Dimension_3] = "#"

or you need to specify the [d/Dimension] = "#" on both sides of your formula.
Maybe Advanced Formulas – How they work helps you in solving your issue.

Best regards
Susanne