CUBEMEMBER Function

Returns a member — such as a region, a year or a measure — from the Data Model or a cube.

Category

Cube

Available in

Excel 2007+ for Windows, Microsoft 365

Returns

A cube member (shows its caption)

Overview

CUBEMEMBER fetches one member of the cube and shows its name in the cell. On its own it's just a label, but other cube functions can point at that cell to use the member.

It's also a quick validity check: if the member doesn't exist, you get #N/A.

Syntax

=CUBEMEMBER(connection, member_expression, [caption])

Arguments

ArgumentRequired?What it does
connectionYesThe connection name in quotes. For the workbook's own Data Model it's "ThisWorkbookDataModel"; for an Analysis Services cube, use the name shown in Data › Queries & Connections.
member_expressionYesText naming the member, such as "[Sales].[Region].&[North]" or "[Measures].[Total Sales]". A tuple of several members goes in braces, or pass a range of member cells.
captionOptionalText to show in the cell instead of the member's own name.

Examples

Example 1 — Row headers for a report

Each cell holds a member; the cell shows the region name.

A2fx=CUBEMEMBER("ThisWorkbookDataModel","[Sales].[Region].&[North]")
A
1Region
2North
3South
4East
The cells look like plain text but hold cube members.
=CUBEMEMBER("ThisWorkbookDataModel","[Sales].[Region].&[North]") Explain

Example 2 — A friendlier caption for a measure

Show “Revenue” while still referring to the Total Sales measure.

=CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Total Sales]","Revenue") Explain

Example 3 — Build the member from a cell

Type a region in D1 and the member follows it.

=CUBEMEMBER("ThisWorkbookDataModel","[Sales].[Region].&["&D1&"]") Explain

Common mistakes

  • #N/A. The member name or hierarchy is misspelled, or the item isn't in the data. Members are matched exactly, including brackets.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.