I am trying to use formula.1 to get data from the Data model and I have managed to use O7:P7 as an array in the Cubermember function.

like this:

This is sample data. =CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel",{"[Table1].[A1].&[A]","[Table1].[A2].&[D]"}),CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of A3]"))

O7 = "[Table1].[A1].&[A]"

P7 = "[Table1].[A2].&[D]"

formula.1 =CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel",O7:P7),CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of A3]"))

This works, But I want to make the array dynamic with named ranges for both variables.

like this:

NamedRange1: ="[Table1].["&A2&"].&["&A1&"]"

NamedRange2: ="[Table1].["&B2&"].&["&B1&"]"

CombinedRange: must be an array with (NamedRange1 and NamedRange2)

I hope I have explained what I want.

I anyone can help that would be grand :)

enter image description here

CodePudding user response：

Use CHOOSE:

=CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel",CHOOSE({1,2},NamedRange1,NamedRange2)),CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of A3]"))