# MDX Functions

This reference section provides information on the MDX functions supported in InterSystems IRIS Business Intelligence.

Also see “Accessing the Samples Shown in This Book,” in the preface.

- %ALL – Enables you to use a member while ignoring any ROW and COLUMN context that uses the hierarchy to which this member belongs. This function is an InterSystems extension to MDX.
- %CELL – Returns the value of another cell in a pivot table, by position. This function is an InterSystems extension to MDX.
- %CELLZERO – Returns the value of another cell in a pivot table or returns zero if that cell has no value. This function is a Business Intelligence extension to MDX.
- %FIRST – Returns the value of the given measure (or other numeric expression) evaluated for the first non-empty member of a set. This function is an InterSystems extension to MDX.
- %KPI – Returns a value from a KPI or plug-in. This function is an InterSystems extension to MDX.
- %LABEL – Given an MDX expression, returns the same expression with a different label for use as a row or column header. %LABEL can also specify formatting for the row or column. This function is a Business Intelligence extension to MDX.
- %LAST – Returns the value of the given measure (or other numeric expression) evaluated for the last non-empty member of a set. This function is an InterSystems extension to MDX.
- %LIST – Returns a comma-separated list of values, given a set of values. This function is an InterSystems extension to MDX and is intended for use in KPIs.
- %LOOKUP – Returns one value from a term list. This function is an InterSystems extension to MDX.
- %MDX – Executes an MDX query outside of the context of the current query and then returns a single result. This function is a Business Intelligence extension to MDX.
- %NOT – Enables you to exclude a single member of a given level. This function is an InterSystems extension to MDX.
- %OR – Enables you to combine multiple members into a single member, for efficiency and to avoid double-counting. This function is an InterSystems extension to MDX.
- %SEARCH – Returns a measure search expression that you can use with the WHERE and %FILTER clauses.
- %SPACE – Inserts a blank row or column with no label. This function is an InterSystems extension to MDX.
- %TERMLIST – Enables you to create a set of members based on a term list. When used with the %OR function, %TERMLIST is particularly useful for filtering. This function is an InterSystems extension to MDX.
- %TIMERANGE – Enables you to define a range of time members, possibly open-ended. This function is an InterSystems extension to MDX.
- %TIMEWINDOW – Returns a set of members of a time dimension that match the given range template.
- %TOPMEMBERS – Returns a set of all members of the first level in the given hierarchy. Or, given a level, it returns a set of all the members of that level. This function is an InterSystems extension to MDX.
- AGGREGATE – Returns the aggregate value for a given measure (or of the current measure), across all elements of a set, according to the aggregation logic of the measure.
- ALLMEMBERS – Returns a set of all members of the given level or hierarchy. Or returns a set of all members of the first hierarchy of a dimension. In either case, any calculated members are also returned.
- ANCESTOR – Returns the ancestor of the given member, within the given level.
- AVG – Returns the average value of a given expression (or of the current measure), across all elements of a set that have a non-null value for that expression.
- BOTTOMCOUNT – Sorts a set and returns a subset from its lower-valued end, given a desired element count.
- BOTTOMPERCENT – Sorts a set and returns a subset from its lower-valued end, given a cutoff percentage that is applied to a total across members.
- BOTTOMSUM – Sorts a set and returns a subset from its lower-valued end, given a cutoff value that is applied to a total across elements.
- CHILDREN – Returns a set that contains the children, if any, of a specified member.
- CLOSINGPERIOD – Returns the last descendent member of the given level, at the same level as the given member. This function is intended primarily for use with time levels.
- COUNT – Returns the count of elements in the given set.
- COUSIN – Given a reference member and a member of a higher level in the same hierarchy, this function finds the ancestor of the reference member at that higher level, determines the relative position of the reference member to that ancestor, and then returns the descendent of the higher member that has the same relative position. This function is intended primarily for use with time levels.
- CROSSJOIN – Returns a set of tuples formed by the cross-product of the specified sets.
- CURRENTMEMBER – Enables you to refer to a member programmatically within an iteration through the members of a hierarchy.
- DESCENDANTS – Returns the members that are the descendants of a given member, within the specified level or levels.
- DISTINCT – Examines a set, removes duplicate elements, and returns a set of the remaining elements.
- EXCEPT – Examines two sets and returns a set that consists of the elements of the first set, except for any elements that are also in the second set. This function optionally eliminates duplicates in that set.
- FILTER – Examines a set and returns the subset in which the given expression is true for each element. The set order is unchanged.
- FIRSTCHILD – Returns the first child of the given member.
- FIRSTSIBLING – Returns the first sibling of the given member.
- HEAD – Returns a subset from the start of a set, using the current order of the set.
- HIERARCHISE – Synonym for HIERARCHIZE.
- HIERARCHIZE – Given a set, returns a set that is in hierarchical order (the order specified by the hierarchy).
- IIF – Returns one of two values, depending on the value of a given logical expression.
- INTERSECT – Returns a set that consists of the elements that occur in both of the two given sets, optionally eliminating duplicates in that set.
- ISNULL – Evaluates a scalar MDX expression and returns either its value or an alternative value (if the value of the expression is null). This function is an InterSystems extension to MDX.
- LAG – Given a level member and a nonnegative integer, this function counts backward in the level and returns a previous member. The details are different for time dimensions and data dimensions.
- LASTCHILD – Returns the last child of the given member.
- LASTSIBLING – Returns the last sibling of the given member.
- LEAD – Given a level member and a nonnegative integer, this function counts forward in the level and returns a later member. The details are different for time dimensions and data dimensions.
- LOG – Returns the base-ten logarithm of the given numeric value.
- LOOKUP – Looks up a given key in a term list and returns a substitute string. This function enables you to perform string replacements within a query. This function is an InterSystems extension to MDX.
- MAX – Returns the maximum value of a given expression (or of the current measure), across all elements of a set.
- MEDIAN – Returns the value closest to the median value, for a given expression (or of the current measure), across all elements of a set that have a non-null value for that expression.
- MEMBERS – Returns a set of all members of the given level or hierarchy, not including any calculated members.
- MIN – Returns the minimum non-null value of a given expression (or of the current measure), across all elements of a set.
- NEXTMEMBER – Returns the next member of the level to which the given member belongs. The details are different for time dimensions and data dimensions.
- NONEMPTYCROSSJOIN – Returns a set that consists of the cross-product of the given sets, excluding any tuples that are null.
- OPENINGPERIOD – Returns the first descendent member of the given level, at the same level as the given member. This function is intended primarily for use with time levels.
- ORDER – Returns a set that is ordered as specified.
- PARALLELPERIOD – Given a reference member, a parent level of that member, and an integer, this function counts backward in the parent level, finds a previous member in that level, and then returns its child that has the same position as the reference member.
- PARENT – Returns the member that is the parent of the given member.
- PERCENTILE – Evaluates a given expression (or the current measure), across all elements of a set, and returns the value that is at a given percentile level.
- PERCENTILERANK – For a given numeric value, this function evaluates a given expression (or the current measure), across all elements of a set, and returns the percentile rank of that expression — the percentage of values that are the same or lower.
- PERIODSTODATE – Returns the set of child or descendent members of the given level, up to and including the given member. This function is intended primarily for use with time levels.
- POWER – Returns the given numeric value raised to the power of the second argument.
- PREVMEMBER – Returns the previous member of the level to which the given member belongs. The details are different for time dimensions and data dimensions.
- PROPERTIES – Returns the value of the given property, for the given member.
- RANK – Returns an integer that indicates the rank of the given member, within the given set.
- ROUND – Evaluates a numeric MDX expression and returns a rounded value. This function is an InterSystems extension to MDX.
- SIBLINGS – Returns a set that contains the specified member and all its siblings.
- SQRT – Returns the square root of the given numeric value.
- STDDEV – Returns the standard deviation of a given expression (or of the current measure), across all elements of a set.
- STDDEVP – Returns the population standard deviation of a given expression, across all elements of a set.
- STDEV – Synonym for STDDEV.
- STDEVP – Synonym for STDDEVP.
- SUBSET – Returns a set of elements from a given set, by position. The first member is at position 0.
- SUM – Returns the sum of a given expression (or of the current measure), across all elements of a set.
- TAIL – Returns a subset from the end of a set, using the current order of the set.
- TOPCOUNT – Sorts a set and returns a subset from its higher-valued end, given a desired element count.
- TOPPERCENT – Sorts a set and returns a subset from its higher-valued end, given a cutoff percentage that is applied to a total across set elements.
- TOPSUM – Sorts a set and returns a subset from its higher-valued end, given a cutoff value that is applied to a total across elements.
- UNION – Returns a set that consists of the elements of the two given sets, optionally eliminating duplicates.
- VAR – Returns the variance of a given expression (or of the current measure), across all elements of a set.
- VARIANCE – Synonym for VAR.
- VARIANCEP – Synonym for VARP.
- VARP – Returns the population variance of a given expression, across all elements of a set.
- VISUALTOTALS – Given a set of members in hierarchical order, returns that set with its visual totals. In the visual totals, the actual value for any higher-level member is replaced with the sum of the values for the children that are included in the query.