CUBEMEMBERPROPERTY function
CUBEMEMBERPROPERTY reads one property of a cube member, such as a product's color or a customer's city, and returns its value. It needs an OLAP cube or Data Model connection and works mainly in Windows desktop Excel.
Example
=CUBEMEMBERPROPERTY("ThisWorkbookDataModel","[Product].[Name].&[Bike]","Color")
Expected result: n/a Not yet checked in desktop Excel
Syntax
CUBEMEMBERPROPERTY(connection, member_expression, property)
-
connection(required) - Name of the cube connection.
-
member_expression(required) - Expression naming the member.
-
property(required) - Name of the member property to return.
Returns: The value of one property of a cube member.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=CUBEMEMBERPROPERTY("NoSuchConnection","[Product].[Bikes]","$Caption") | #N/A | Unknown connection name |
=CUBEMEMBERPROPERTY("ThisWorkbookDataModel","[Product].[Bikes]","$Caption") | #N/A | ThisWorkbookDataModel without a model |
=CUBEMEMBERPROPERTY("","[Product].[Bikes]","$Caption") | #N/A | Empty connection string |
=CUBEMEMBERPROPERTY(1,"[Product].[Bikes]","$Caption") | #N/A | Connection is a number |
=CUBEMEMBERPROPERTY(Data!D1,"[Product].[Bikes]","$Caption") | #N/A | Connection from blank cell |
The Data sheet these formulas read
A cell that starts with = holds that formula.
D
1
Data
Watch out for
- Returns #N/A when the member or property does not exist.
Where it works
- Excel
- CUBEMEMBERPROPERTY is in every version of Excel from 2007 on. Needs an OLAP cube or Data Model connection; Windows desktop Excel only for most uses.
- Google Sheets
- No direct equivalent. No cube support in Google Sheets.
Part of Cube functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .