Cube

CUBESET Function in Excel

Defines a calculated set of members by sending a set expression to the cube.

Syntax

  • =CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

Arguments

  • connection (required): Connection string to cube
  • set_expression (required): MDX set expression
  • caption (optional): Display text
  • sort_order (optional): Sort order (1-6)
  • sort_by (optional): Value to sort by

Examples

  • =CUBESET("Sales", "[Product].[Category].Members") - Get all categories - Result: Set

CUBESET and data models

  • Validate members with CUBEMEMBER before CUBEVALUE.
  • Confirm the connection name matches your Power Pivot or OLAP model.
  • Explore summaries with [Pivot tables guide](/guides/excel-pivot-tables-guide/).

Common errors

  • #N/A if set expression invalid

Use cases

  • OLAP reporting
  • Dynamic sets
  • Filtering

Frequently asked questions

  • What is CUBESET used for? CUBESET creates a collection of members that you can iterate through with CUBERANKEDMEMBER. Use it to get all products in a category, top 10 customers, or any filtered/sorted list from your data model.
  • How do I get Top 10 items with CUBESET? Use MDX TopCount: =CUBESET(conn, "TopCount([Product].[Name].Members, 10, [Measures].[Sales])", "Top 10"). Then use CUBERANKEDMEMBER to extract each item: =CUBERANKEDMEMBER(conn, A1, ROW()-1).
  • What are the sort_order values? 1=Ascending by caption, 2=Descending by caption, 3=Ascending by sort_by value, 4=Descending by sort_by value, 5=Ascending natural order, 6=Descending natural order. Use 4 with a measure for Top N lists.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.