CUBEMEMBER - multiple filters set dynamically

Question

Hello

I am using CUBEMEMBER function to fetch some values in Excel from Analysis Services Cubes. I converted a Pivot table into functions. One of the function looks like this:

=CUBEMEMBER("CorrelationCube",{"[Curve].[Curve Name].[Brent]","[Terms].[Term Name].[M+1]"})

I want to introduce parameters so that I can supply Curve Name and Term Name from other cells, let's assume in cells A1 and A2 respectively.

A1 have text/value Brent

A2 have text/value M+1

How to append the above CUBEMEMBER function to get values from A1 and A2?

=CUBEMEMBER("CorrelationCube",{"[Curve].[Curve Name].[TEXT FROM A1]","[Terms].[Term Name].[TEXT FROM A2]"})

Just to mention, I could easily do that when I have just one parameter. Example - if I have to supply just Term and assuming term text is supplied in cell A1, I can easily write something like

=CUBEMEMBER("CorrelationCube","[Terms].[Term Name].[" & A1 &"]")

Thank You

Answer 1

Hi,

Try this

1. In cell B1, type

="[Curve].[Curve Name].["&A1&"]"

2. In cell B2, type

="[Terms].[Term Name].["&A2&"]"

Change your formula to

=CUBEMEMBER("CorrelationCube",B1:B2)

Hope this helps.

Answer 2

Hi,

Try this

=CUBEMEMBER("CorrelationCube",{"[Curve].[Curve Name].["&$A$1&"]","[Terms].[Term Name].["&$A$2&"]"})

Answer 3

Hi,

Try this

=CUBEMEMBER("CorrelationCube",B1:C1)

C1 has

="[Terms].[Term Name].["&A2&"]"

B1 is the same as the earlier post.

Hope this helps.

Answer 4

Excel accepts the formula =CUBEMEMBER("CorrelationCube",B1:B2) but it returns value as if we are just supplying B2.

So I think "{ }" symbols are playing some role.

Answer 5

Thanks for the reply Ashish.

It doesn't work - I think because formula contains "{" symbol, so you can't treat it like a string. That's why I could do this very easily when I have just one condition to pass (in which case it is just a string), please refer my question for details.

Problem Setup:

1. Open an empty workbook
2. Paste Brent in A1 and M+1 in A2
3. Paste your formula in any cell

=CUBEMEMBER("CorrelationCube",{"[Curve].[Curve Name].["&$A$1&"]","[Terms].[Term Name].["&$A$2&"]"})

Does Excel allow you to enter that formula?

(Sure you can not get values as you won't have access to CorrelationCube, but if Excel accepts your formula, we are good.)

Regards