# 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 cube 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 cube 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 cube 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 cube-sided tuple expressions for calculated measures.
- UCASE | LCASE The
`UCASE`converts all alphabetic characters in a string to all uppercase.`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.