EPMDimensionOverrideRange

[b5noj]

Hi

Can I use the EPM dimension override range function to override one dimension for a base list of members in an excel range? 

=EPMDimensionOverrideRange("000","PROFITCENTRE","BAS("&$A$3:$A$5&")")

When I try the below it says the third parameter isn't valid. What's the right syntax for pulling base members from a range of cells?

[johnnbc]

It expects a range and you are giving it a mix of a range and text. Remove the BAS bit (and put into the cells A3,A4 and A5 separately)

[b5noj]

Ok what am I doing wrong here. I've tried two approaches

1) Green cell on the screenshot. This method works and the report refreshes instantly.

=EPMDimensionOverride("000","PROFITCENTRE","BAS("&F2&"),BAS("&F3&")")

2) Orange cell on the screenshot. Trying to use the range method and linking it to two additional columns in the table which add the BAS onto the members in column F.

'=EPMDimensionOverrideRange("000","PROFITCENTRE",Table1[Manual Dimension Override List for Range])

and in the table helper column to get base ="BAS("&[@[Manual Dimension Override list]]&")"

Using method 2 gives me no errors but the report just hangs on "Sheet1: Restoring Excel Calculation..." and stays there for at least 15 mins when I've just given up and force closed excel.

[johnnbc]

The first issue is you are attempting to reference the table rather than the column 2 which contains the BAS(*) - I tried this and it crashed. The second problem seems to be that EPMDimensionOverrideRange requires a rang, not table. Change the first parameter to G2:G3 and it should work (also technically the second parameter is supposed to be a range rather than PROFITCENTRE - but it does work both ways)

[b5noj]

No I'm referencing the specific column within the table not just the table. Red is the table name, Green is the table column.

=EPMDimensionOverrideRange("000","PROFITCENTRE",Table1[Manual Dimension Override List for Range])

I've tried changing this to be non tabular ranges 

=EPMDimensionOverrideRange("000","PROFITCENTRE",G2:G3)

The outcome is still the same, it just hangs forever on the same screen

Also tried putting the dimension name in cell ranges too just to check it's not that and it's the same outcome

=EPMDimensionOverrideRange("000",$H$2:$H$3,$G$2:$G$3)

[b5noj]

OK got it working even with table ranges but seems your range can't contain formulas.

To illustrate. Referencing the yellow cells which are a formula combing text and cell contents to add the BAS(*) causes excel to crash 

Yellow override - =EPMDimensionOverrideRange("000","PROFITCENTRE",Table1[Manual Dimension Override List for Range])

Yellow cell ref range = formula to add BAS(*) to the member entered in the first column "="BAS("&[@[Manual Dimension Override list]]&")""

Blue ticked override - "=EPMDimensionOverrideRange("000","PROFITCENTRE",Table1[Manual Dimension Override List for Range HardTyped])"

Blue ticked cell ref range = Hard typed "BAS(P1636400)" etc

Seems a shame as it means I'll have to instruct the end users to enter the member with BAS(*) wrapped around it. Would be far simpler to allow them to enter the member that they recognise and then for me to add the right text in another cell and then to ref that cell in the dimension override