CUBESET function
CUBESET defines a group of cube members that other cube functions can then count, rank or list. The cell shows only the set's caption, so pair it with CUBERANKEDMEMBER to see the individual items.
Example
=CUBESET("ThisWorkbookDataModel","[Product].[Category].Children","Categories")
Expected result: n/a Not yet checked in desktop Excel
Syntax
CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
Arguments in [square brackets] are optional.
-
connection(required) - Name of the cube connection.
-
set_expression(required) - Expression defining the set of members or tuples.
-
caption(optional) - Text displayed in the cell.
-
sort_order(optional) - Number from 0 to 6 selecting the sort direction and type.
-
sort_by(optional) - What to sort on, such as a measure.
Returns: A defined set of cube items, shown as the caption.
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 |
|---|---|---|
=CUBESET("NoSuchConnection","[Product].[All].Children") | #N/A | Unknown connection name |
=CUBESET("ThisWorkbookDataModel","[Product].[All].Children") | #N/A | ThisWorkbookDataModel without a model |
=CUBESET("","[Product].[All].Children") | #N/A | Empty connection string |
=CUBESET(1,"[Product].[All].Children") | #N/A | Connection is a number |
=CUBESET("NoSuchConnection","[Product].[All].Children","c",1) | #N/A | Sort order 1 |
=CUBESET("NoSuchConnection","[Product].[All].Children","c",7) | #N/A | Sort order 7 (invalid) |
=CUBESET(Data!D1,"[Product].[All].Children") | #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
- The cell shows only the caption; use CUBERANKEDMEMBER to list the items.
- Large sets can slow calculation.
Where it works
- Excel
- CUBESET 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 .