Excel CUBEMEMBER Function

The CUBEMEMBER function retrieves a member or tuple from a cube if it exists. Otherwise, a #N/A error value will be returned.

Syntax

=CUBEMEMBER(connection, member_expression, [caption])

Arguments

Return Value

The CUBEMEMBER function returns the member or tuple from a cube.

Function Notes

• The #GETTING_DATA… message is displayed while the data is being retrieved.
• CUBEMEMBER returns the #NAME? error value if:
  • connection is not a valid workbook connection stored in the workbook, e.g., ThisWorkbookDataModel;
  • Online Analytical Processing (OLAP) server is unavailable, not running, or returned an error message.
• CUBEMEMBER returns the #VALUE! error value if:
  • There is one or more invalid elements in the tuple;
  • member_expression is longer than 255 characters. Note: You can enter the text string in a cell and then reference the cell as the argument as a workaround.
• CUBEMEMBER returns the #N/A error value if:
  • member_expression is not valid;
  • The member to be returned from the cube does not exist;
  • The set contains one or more members with a different dimension than the other members;
  • The tuple in the member_expression argument is not valid since there is no intersection for the specified values.
• Arguments of CUBEMEMBER should be closed with double quotes ("").
• Names of members, dimensions or tables, etc. you write in member_expression should be wrapped in square brackets ([ ]).

Example to retrieve memebers

I have a table here named “sales2021” which includes the information of sales and rating of different products of 2 categories across the year 2021. To use the CUBEMEMBER function to retrieve members from the table, you should first add the data from this table to the Data Model in the existing workbook, its name will always be ThisWorkbookDataModel. Then please copy or enter the formula below in cell where you want to fetch a member (here I will fetch Caps as an example), and press Enter to get the result:

=CUBEMEMBER("ThisWorkbookDataModel","[sales2021].[Product].&[Caps]")

√ Note: In the formula above, CUBEMEMBER returns the member named Caps of the Product category from the table sales2021 in the ThisWorkbookDataModel cube. To fetch January, you can use the formula:

=CUBEMEMBER("ThisWorkbookDataModel","[sales2021].[Month].&[January]"

Example with a measure name

To use CUBEMEMBER to summarize members from the table, such as to get the sum of sales in different months, the average ratings of different products, you can use a measure name. In this example, I entered the below formula in cell G5 with a measure expression that will apply against the entire data set.

=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of Sales]")

√ Note: You can add a caption argument, e.g., “Sales” or “Month”, to display a name you want in cell G5 instead of Sum of Sales, since the measure has a redundant name that could make the report hard to read, for example:

=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of Sales]","Sales")

Related functions