CUBEMEMBER function
Picks one item (a member or tuple) from the Data Model so CUBEVALUE can use it — like a row or column heading in a PivotTable.
Syntax
=CUBEMEMBER(connection, member_expression, [caption])How to use CUBEMEMBER in Excel, step by step
CUBEMEMBER doesn’t run in a web practice sheet because it reads a data model (Power Pivot / OLAP) connection. Follow these steps in Excel itself.
You’ll need
- A Data Model or OLAP connection
- Put your data in a table. Click inside your data, press Ctrl+T, tick “My table has headers” and click OK. Give it a clear name on the Table Design tab (for example Sales).
- Add the table to the Data Model. Go to Insert › PivotTable › From Table/Range, tick “Add this data to the Data Model” and click OK. Excel now has a connection called ThisWorkbookDataModel.
- Build a quick PivotTable first. Drag a field (for example Region) to Rows and a number (for example Amount) to Values. This creates the measure the CUBE functions will read, such as [Measures].[Sum of Amount].
- Let Excel write the formulas for you. Click the PivotTable, then PivotTable Analyze › OLAP Tools › Convert to Formulas. Every cell becomes a CUBEMEMBER or CUBEVALUE formula you can copy and edit.
- Create a heading cell. Type =CUBEMEMBER("ThisWorkbookDataModel","[Sales].[Region].&[North]") — the cell shows “North”.
- Use a friendlier caption (optional). Add a third argument to change what the cell shows: =CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of Amount]","Total sales").
- Feed it to CUBEVALUE. Refer to the cell from CUBEVALUE, e.g. =CUBEVALUE("ThisWorkbookDataModel",$B$1,A5).
Example
=CUBEMEMBER("ThisWorkbookDataModel","[Sales].[Region].&[North]")Result: North
Members are written as [Table].[Column].&[Item].