Excel - Cube Functions

CUBE Functions in Excel

CUBEKPIMEMBER

The CUBEKPIMEMBER function returns a Key Performance Indicator (KPI) property from the cube, such as value, goal, or status.

Syntax:

=CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])

Example:

=CUBEKPIMEMBER("PowerPivot Data", "Sales KPI", "Value")

Result: Returns the value measure of the KPI "Sales KPI".

CUBEMEMBER

The CUBEMEMBER function returns a member or tuple from the cube. It is used to extract a specific dimension value.

Syntax:

=CUBEMEMBER(connection, member_expression, [caption])

Example:

=CUBEMEMBER("PowerPivot Data", "[Date].[Year].&[2023]")

Result: Retrieves the year 2023 from the Date hierarchy.

CUBEMEMBERPROPERTY

The CUBEMEMBERPROPERTY function returns the value of a property for a given member in the cube.

Syntax:

=CUBEMEMBERPROPERTY(connection, member_expression, property)

Example:

=CUBEMEMBERPROPERTY("PowerPivot Data", "[Product].[Category].&[1]", "MEMBER_CAPTION")

Result: Returns the display caption of the product category with ID 1.

CUBERANKEDMEMBER

The CUBERANKEDMEMBER function returns the nth or ranked member of a set, which is useful for top/bottom analysis.

Syntax:

=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

Example:

=CUBERANKEDMEMBER("PowerPivot Data", "[Product].[Product Name].Members", 1)

Result: Returns the top product in the Product Name hierarchy.

CUBESET

The CUBESET function defines a set of members or tuples from the cube by evaluating a set expression. It can be used for filtering or ranking purposes.

Syntax:

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

Example:

=CUBESET("PowerPivot Data", "[Product].[Category].Children")

Result: Returns all children of the Product Category dimension.

CUBESETCOUNT

The CUBESETCOUNT function returns the number of items in a set defined by a CUBESET function.

Syntax:

=CUBESETCOUNT(set)

Example:

=CUBESETCOUNT(A2)

Result: If cell A2 contains a valid CUBESET, returns how many items are in that set.

CUBEVALUE

The CUBEVALUE function returns the value of a cube cell based on member expressions provided. You can supply one or more dimensions.

Syntax:

=CUBEVALUE(connection, [member_expression1], [member_expression2], ...)

Example:

=CUBEVALUE("PowerPivot Data", "[Measures].[Total Sales]", "[Product].[Category].&[1]")

Result: Returns the total sales for product category ID 1.