Skip to main content

MDX Reference

This section lists the MDX functions and operators allowed in a calculated measure formula.

  • ABS The ABS function returns the absolute value of the argument.
  • AGGREGATE The AGGREGATE function Returns a number that is calculated by aggregating over the cells returned by the set expression.
  • ALL Refers to the All level with a single member called the All member. Used to constrain a tuple expression.
  • ALLMEMBER Similar to the ALL(<Table>) DAX function, this function evaluates a given tupleExpression as if all other model hierarchy references explicitly reference the hierarchy's ALL member.
  • ALLMEMBEREXCEPT Similar to the ALLEXCEPT DAX function, this function evaluates a given tupleExpression as if all other model hierarchy references explicitly reference the hierarchy's ALL member, with the exception of the hierarchies listed in hierarchySet.
  • ANCESTOR A function that returns the ancestor of a specified member at a specified level or at a specified distance from the member.
  • Arithmetic Operators (MDX) Arithmetic operators perform basic math operations on their operands. AtScale supports arithmetic operators in Calculated Measure formulas. Notice the NULL handling behavior in MDX is different than SQL.
  • AVG The AVG function returns the average of a Measure evaluated over a given set.
  • CASE The CASE function evaluates each row in the dataset according to one or more input conditions, and outputs the specified result when the input conditions are met.
  • CBOOL Casts the value to a Boolean.
  • CDBL Casts the value to a Double.
  • CDEC Casts the value to a Decimal number.
  • CEILING The CEILING function returns the smallest integer value greater than or equal to the argument.
  • CHILDREN Returns a naturally ordered set that contains the children of a specified member.
  • CINT Casts the value to an Integer.
  • CLONG Casts the value a Long.
  • Comparison Operators (MDX) Comparison operators evaluate to a Boolean data type, returning true or false based on the outcome of the tested condition.
  • COUNT The COUNT function returns the size of a given set.
  • CSTR Casts the value to a String.
  • CURRENTMEMBER.NAME The CURRENTMEMBER.NAME function returns the current member value of the specified dimension attribute during iteration.
  • DatesPeriodsToDate Returns the set of dates corresponding to the range of Day members beginning on the first sibling member of Level_Expresson and ending on the member described by Member_Expression.
  • DatesMTD An alias for DatesPeriodsToDate with the Level_Expression argument dynamically set to the Month level of the Member_Expression argument's Time hierarchy.
  • DatesQTD An alias for DatesPeriodsToDate with the Level_Expression argument dynamically set to the Quarter level of the Member_Expression argument's Time hierarchy.
  • DatesWTD An alias for DatesPeriodsToDate with the Level_Expression argument dynamically set to the Week level of the Member_Expression argument's Time hierarchy.
  • DatesYTD An alias for DatesPeriodsToDate with the Level_Expression argument dynamically set to the Year level of the Member_Expression argument's Time hierarchy.
  • DAY Returns the day of month from the Date or DateTime measure as an integer.
  • DESCENDANTS Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.
  • DIMENSION UNIQUE NAME The unique name of the dimension to which this member belongs.
  • DIVIDE Performs division and returns alternate result or BLANK() on division by zero.
  • E Returns the value of mathematical constant e.
  • EXP Returns the mathematical constant e raised to the specified power.
  • FIRSTCHILD Returns the first child of an input member.
  • FIRSTSIBLING Returns the first child of the parent of a member, otherwise known as the member's first sibling.
  • FLOOR The FLOOR function returns the largest integer value less than or equal to the argument.
  • HIERARCHY_UNIQUE_NAME The unique name of the hierarchy to which this member belongs.
  • HOUR Returns the hour of day component of a DateTime or Timestamp measure as an integer.
  • IIF The IIF function evaluates one of two different expressions, depending on whether a Boolean condition is true or false.
  • INSTR The InStr function returns the position of the first occurrence of one string within another string.
  • ISEMPTY The ISEMPTY function evaluates if a cell in a model is empty or not.
  • KEY The value of the member key in the original data type.
  • LAG The LAG function returns a member that precedes the specified member by a specified number of positions in its level.
  • LASTCHILD Returns the last child of a specified member.
  • LASTSIBLING Returns the last child of the parent of a member, otherwise known as the member's last sibling.
  • LEAD The LEAD function returns a member that follows the specified member by a specified number of positions in its level.
  • LEFT The LEFT function returns a string of a specified number of characters from the left side of a specified string.
  • LEN Returns a Long containing the number of characters in a string or the number of bytes required to store a variable.
  • LEVEL Returns the Name of a dimension member's Level.
  • LEVEL_NUMBER The distance of the member from the root of the hierarchy.
  • LEVEL_UNIQUE_NAME The unique name of the level to which the member belongs.
  • LOG Returns the base x logarithm of the argument.
  • LOG2 Returns the base 2 logarithm of the argument.
  • LOG10 Returns the base 10 logarithm of the argument.
  • Logical Operators (MDX) Logical operators are used to combine arguments in a Boolean expression (an expression that evaluates to true or false).
  • MAX The MAX function returns the max of a Measure evaluated over a given set.
  • MEMBER_CAPTION A label or caption associated with the member. The caption is primarily for display purposes.
  • MEMBER_KEY The value of the member key in the original data type.
  • MEMBER_LEVEL_NUMBER The distance of the member from the root of the hierarchy.
  • MEMBER_NAME The name of the member.
  • MID The MID function returns a substring of a string argument.
  • MIN The MIN function returns the min of a Measure evaluated over a given set.
  • MINUTE Returns the minute of hour component of a DateTime or Timestamp measure as an integer.
  • MONTH Returns the Month component of a Date or DateTime measure as an integer.
  • NEXTMEMBER The NEXTMEMBER function returns the member that follows the specified member in the level.
  • NOW Returns the DateTime from the data warehouse.
  • NULLEXCEPT Evaluates the given tuple expression when the query includes any of the hierarchies listed in the hierarchySet argument, else returns NULL.
  • PARALLELPERIOD The PARALLELPERIOD function returns a member from the same relative position in a previous period as the specified member.
  • PARENT Returns the parent member of the specified member.
  • PARENT_COUNT The number of parents that this member has.
  • PARENT_LEVEL The distance of the member's parent from the root level of the hierarchy.
  • PERIODSTODATE The PERIODSTODATE function returns a set of sibling members from the specified level of a time dimension, beginning with the first sibling and ending with a specified member.
  • PI Returns the value of mathematical constant Pi.
  • POW Returns a base raised to a power.
  • PREVMEMBER The PrevMember function returns the member that precedes the specified member in the level.
  • PROPERTIES Returns the value of the specified member for the specified member property.
  • RAND - Returns a random number between 0 and 1 based on an optional seed.
  • RIGHT - The Right function returns a string of a specified number of characters from the right side of a specified string
  • ROUND - Round a number to an integer or the specified number of fractional digits.
  • SECOND Returns the second of minute component of a DateTime or Timestamp measure as an integer.
  • Set Operators (MDX) Set operators perform operations on members or sets, and return a set.
  • SIBLINGS Returns the siblings of a member, including the member.
  • SIGN The SIGN function returns -1, 1, or 0 if the sign of the argument is either negative, positive, or undefined (zero).
  • SQLSUM The SQLSUM function returns the sum of its arguments.
  • SUM The SUM function returns the sum of a Measure evaluated over a given set.
  • Trigonometric Functions AtScale supports the following MDX trigonometric functions in a calculated measure formula.
  • TRIM | LTRIM | RTRIM The TRIM function removes both leading and trailing spaces from the specified string. LTRIM removes just leading spaces, and RTRIM removes just trailing spaces.
  • TRUNCATE Returns the integral component of a floating-point or fixed-precision number.
  • Tuple Expressions AtScale supports simple model-sided tuple expressions for calculated measures.
  • UCASE | LCASE The UCASE converts all alphabetic characters in a string to all upper case. LCASE converts all alphabetic characters in a string to all lower case.
  • VBA Date Functions Enable you to create default hierarchical members on time hierarchies; for example, the current date or previous year.
  • YEAR Returns the Year component of a Date or DateTime measure as an integer.