CUBEMEMBERPROPERTY

What it does

CUBEMEMBERPROPERTY(connection, member_expression, property)

CUBEMEMBERPROPERTY reads an attribute of a cube member — not a measure, not an aggregate, but a piece of descriptive data hanging off the member itself: a product's colour, a store's city, an employee's hire date.

Given a connection, an MDX expression naming a member, and a property name, the function returns the value of that property for that member. Microsoft's description pairs it with a validation role, as it does for CUBEMEMBER: "use to validate that a member name exists within the cube and to return the specified property for this member."

The distinction that matters is property versus measure, and it is the reason this function exists alongside CUBEVALUE:

• A measure is a number the cube aggregates over the dimensions you did not pin. Retrieved with CUBEVALUE. Adding a second product to the slice changes the answer.
• A member property is a fixed attribute of one member. Retrieved with CUBEMEMBERPROPERTY. It does not aggregate, does not depend on any other dimension, and has no meaning for a set — only for a single member.

If you find yourself wanting the property "of a set", the model is telling you to use CUBERANKEDMEMBER (or CUBESET plus iteration) to get down to one member first.

Which properties exist is a fact about the cube's schema, not about Excel. There is no closed list, and a property name that works against one cube is meaningless against another.

Arguments

CUBEMEMBERPROPERTY(connection, member_expression, property)

All three are required; there is no optional caption here, unlike CUBEMEMBER and CUBERANKEDMEMBER — the result is a property value, not a member, so there is no caption to override.

• connection (required) — text; the name of a connection stored in the workbook, used as a lookup key rather than as a connection string.
• member_expression (required) — text holding an MDX expression naming a member within the cube. Note the narrowing relative to CUBEMEMBER, whose corresponding argument also accepts a tuple: a property belongs to one member.
• property (required) — text naming the property, or a reference to a cell containing that name. This is the argument readers misjudge: it is a schema-level identifier from the cube's own metadata, not a display label and not something Excel validates in advance.

Try it

This example needs an Excel cube connection. OneCalc cannot provide that connection in this preview.

Quirks

Result and edge cases

The result is whatever the property holds — commonly text, but a property may equally be a date or a number, so a workbook should not assume a return kind. There is no documented coercion or formatting step; the value arrives as the cube supplies it.

While the request is outstanding the cell displays #GETTING_DATA… — a transient extended-family placeholder catalogued in the value universe, not a failure.

Power Pivot data models are out of scope, and the documentation says so. Microsoft states that this function does not work with Excel Data Models edited in Power Pivot, because those are not multidimensional cubes. This is worth flagging loudly: much of what users call "the cube" in a modern workbook is a tabular Data Model, and other CUBE functions do work against it. CUBEMEMBERPROPERTY is the family member that draws the line.

Errors

As documented by Microsoft on the page linked below:

• #NAME? — the connection name is not a valid workbook connection; or the OLAP server is not running, not available, or returns an error message.
• #N/A — the member_expression syntax is incorrect, or the member it names does not exist in the cube; or the expression refers to a session-scoped calculated member or named set whose PivotTable has been deleted or converted to formulas.

The documentation does not state what happens when the member is valid but the property name is not. That is the obvious asymmetry in the error list — two of the three arguments have documented failure modes and the third does not — and it is listed as a probe below.

How to implement

Notes for implementers

• There is no kernel. The function is a metadata request; the whole of its behaviour is connection resolution, MDX member resolution, and property lookup in the cube schema.
• The return kind is not fixed by the function. An implementation that always returns text will diverge on date- and number-valued properties, and the divergence will only show up in downstream arithmetic.
• The Power Pivot exclusion is a capability boundary, not a bug: an implementation backed by a tabular model has no member properties to return and must decide, explicitly, what to do.
• property accepting either a literal or a cell reference is ordinary argument handling, but the cell-reference form is how workbooks parameterize property choice from a dropdown; an implementation must resolve it as a plain value, unlike the family's identity-bearing arguments.

Rust code

This entry has no Rust implementation yet.

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• CUBEMEMBER — the sibling that returns the member itself rather than one of its attributes. CUBEMEMBERPROPERTY is what you reach for once CUBEMEMBER has confirmed the member exists.
• CUBEVALUE — the measure-returning counterpart. The property/measure distinction above is the whole reason both exist.
• CUBEKPIMEMBER — also returns a component of a cube object, but a KPI component is a member expression usable as a coordinate, whereas a member property is a terminal value. The two look similar in a formula and behave quite differently downstream.

Readers confuse member properties with PivotTable "Show Details" fields and with GETPIVOTDATA. GETPIVOTDATA reads from a PivotTable rendered on a sheet; CUBEMEMBERPROPERTY reads cube metadata with no PivotTable involved.

Named variants

No named variants are recorded yet.