Skip to main content

Quick Function Reference (MDX)

The following table summarizes the syntax and return type of each supported MDX function in Business Intelligence.

Function Syntax Return Type
%ALL member_expression.%ALL member
%CELL %CELL(relative_column_position, relative_row_position) number or string
%CELLZERO %CELLZERO(relative_column_position, relative_row_position) number or string
%FIRST %FIRST(set_expr, optional_numeric_expr) number
%KPI %KPI(kpi_name, kpi_prop_name, kpi_series_name, parm, value, parm, value,...) number
%LABEL %LABEL(MDX_expr, label, format_details, solve_order, cell_style, heading_style) same as MDX_expr
%LAST %LAST(set_expr, optional_numeric_expr) number
%LIST %LIST(set_expr) string (comma-separated list)
%LOOKUP %LOOKUP(termlist, key, field, default) number or string
%MDX %MDX(“MDX select query”, parm, value, parm, value, parm, value,...) number or string
%NOT member_expression.%NOT member
%OR %OR(set_expr) member
%SEARCH %SEARCH.&[comparison_expression] measure search expression
%SPACE %SPACE() empty space
%TERMLIST %TERMLIST(term_list_name, INCLUDE | EXCLUDE) set
%TIMERANGE %TIMERANGE(start_member, end_member, INCLUSIVE | EXCLUSIVE) member
%TIMEWINDOW %TIMEWINDOW(set_expr, start_member, optional_end_member) set of members
%TOPMEMBERS

level_expr.%TOPMEMBERS

hierarchy_expr.%TOPMEMBERS

dimension_expr.%TOPMEMBERS

set of members
AGGREGATE AGGREGATE(set_expr, optional_numeric_expr) number
ALLMEMBERS

level_expr.ALLMEMBERS

hierarchy_expr.ALLMEMBERS

dimension_expr.ALLMEMBERS

set of members
ANCESTOR ANCESTOR(member_expr, ancestor_level) member
AVG AVG(set_expr, optional_numeric_expr) number
BOTTOMCOUNT BOTTOMCOUNT(set_expr, element_count, optional_ordering_expr) set of members or tuples
BOTTOMPERCENT BOTTOMPERCENT(set_expr, element_count, optional_ordering_expr) set of members or tuples
BOTTOMSUM BOTTOMSUM(set_expr, element_count, optional_ordering_expr) set of members or tuples
CHILDREN member_expr.CHILDREN set of members
CLOSINGPERIOD CLOSINGPERIOD(ancestor_level, member_expr) member
COUNT

COUNT(set_expr)

COUNT(set_expr, EXCLUDEEMPTY)

number
COUSIN COUSIN(member_expr, higher_member_expr) member
CROSSJOIN

CROSSJOIN(set_expr1, set_expr2)

NON EMPTY CROSSJOIN(set_expr1, set_expr2)

set of tuples
CURRENTMEMBER

hierarchy_expr.CURRENTMEMBER

dimension_expr.CURRENTMEMBER

member
DESCENDANTS DESCENDANTS(member_expression, level_expression, OPTIONAL_FLAG)

DESCENDANTS(member_expression, level_offset, OPTIONAL_FLAG)

set of members
DISTINCT DISTINCT(set_expr) set
EXCEPT

EXCEPT(set_expr1, set_expr2, ALL)

EXCEPT(set_expr1,set_expr2)

set
FILTER FILTER(set_expr, logical_expr) set
FIRSTCHILD member_expr.FIRSTCHILD member
FIRSTSIBLING member_expr.FIRSTSIBLING member
HEAD HEAD(set_expr, optional_integer_expr, optional_sample_flag) set
HIERARCHIZE, HIERARCHISE

HIERARCHIZE(set_expr)

HIERARCHIZE(set_expr, POST)

set of members
IIF IIF(logical_expr, expression1, expression2) number or string
INTERSECT INTERSECT(set_expr1, set_expr2) set
ISNULL ISNULL(scalar_expression,scalar_value_if_null) number or string
LAG member_expr.LAG(optional_nonnegative_integer_expr) member
LASTCHILD member_expr.LASTCHILD member
LASTSIBLING member_expr.LASTSIBLING member
LEAD member_expr.LEAD(optional_nonnegative_integer_expr) member
LOG LOG(numeric_expr) number
LOOKUP LOOKUP(term_list_name, lookup_value, default, alternative_field) string
MAX MAX(set_expr, optional_numeric_expr) number
MEDIAN MEDIAN(set_expr, optional_numeric_expr) number
MEMBERS

level_expr.MEMBERS

hierarchy_expr.MEMBERS

dimension_expr.MEMBERS

set of members
MIN MIN(set_expr, optional_numeric_expr) number
NEXTMEMBER member_expr.NEXTMEMBER member
NONEMPTYCROSSJOIN NONEMPTYCROSSJOIN(set_expr1, set_expr2) set of tuples
OPENINGPERIOD OPENINGPERIOD(ancestor_level, member_expr) member
ORDER

ORDER(set_expr, ordering_expr, ASC | DESC | BASC | BDESC)

ORDER(set_expr, ordering_expr)

set
PARALLELPERIOD PARALLELPERIOD(level_expr, offset, member_expr) member
PARENT member_expr.PARENT member
PERCENTILE PERCENTILE(set_expr, numeric_expr, numeric_expr, optional_percentile_value) number
PERCENTILERANK PERCENTILERANK(set_expr, numeric_expr, comparison_value) number
PERIODSTODATE PERIODSTODATE(ancestor_level, member_expr) set of members
POWER POWER(numeric_expr,numeric_expr_for_power) number
PREVMEMBER member_expr.PREVMEMBER member
PROPERTIES member_expr.PROPERTIES(property_name) string
RANK RANK(tuple_expr, set_expr, optional_numeric_expr) number
ROUND ROUND(numeric_expr,decimal_places) number
SIBLINGS member_expr.SIBLINGS set of members
SQRT SQRT(numeric_expr) number
STDDEV, STDEV STDDEV(set_expr, optional_numeric_expr) number
STDDEVP, STDEVP STDDEVP(set_expr, optional_numeric_expr) number
SUBSET SUBSET(set_expr, first_element_expr, optional_element_count) set
SUM SUM(set_expr, optional_numeric_expr) number
TAIL TAIL(set_expr, optional_integer_expr) set
TOPCOUNT TOPCOUNT(set_expr, element_count, optional_ordering_expr) set of members or tuples
TOPPERCENT TOPPERCENT(set_expr, element_count, optional_ordering_expr) set of members or tuples
TOPSUM TOPSUM(set_expr, element_count, optional_ordering_expr) set of members or tuples
UNION

UNION(set_expr1,set_expr2)

UNION(set_expr1,set_expr2, ALL)

set
VAR, VARIANCE VAR(set_expr, optional_numeric_expr) number
VARP, VARIANCEP VARP(set_expr, optional_numeric_expr) number
VISUALTOTALS VISUALTOTALS(set_expr, optional_parent_name_pattern) set of members
FeedbackOpens in a new tab