MDX Functions (Other)
This section covers a variety of MDX functions that do not fall into the more specific categories of aggregation, set, string, or numeric functions. These functions provide utility for tasks such as working with dates, managing scope, and retrieving information about the cube structure.
COALESCE
COALESCE(expression1 [, expression2, ...])Evaluates a list of expressions and returns the first non-NULL expression. If all expressions are NULL, it returns NULL.
Example:
COALESCE([Measures].[Sales Amount], [Measures].[Sales Quantity] * [Measures].[Unit Price])DATEDIFF
DATEDIFF(interval, date1, date2 [, dayOfWeek] [, weekOfYear])Calculates the difference between two dates, returning the number of intervals between them. Intervals can be specified as years, months, days, etc.
Example:
DATEDIFF(DAY, [Date].[Calendar].&[20230101], [Date].[Calendar].&[20230115])CURRENTMEMBER
CURRENTMEMBER(dimension_expression)Returns the current member of the specified dimension that is currently evaluated in the context of a member or tuple.
Example:
[Product].&[123].CURRENTMEMBERVISUALTOTAL
VISUALTOTAL(member_expression)Returns the visual total of a specified member expression. This is often used in calculations to represent subtotals or grand totals that respect the current filter context.
Example:
VISUALTOTAL([Measures].[Sales])IIF
IIF(condition, true_expression, false_expression)Returns one of two values based on a condition. If the condition is true, it returns the true_expression; otherwise, it returns the false_expression.
Example:
IIF([Measures].[Sales Amount] > 1000, "High Sales", "Low Sales")MEMBERPROPERTIES
MEMBERPROPERTIES(member_expression [, property_name1] [, property_name2, ...])Returns the properties of a specified member. You can retrieve all properties or specify individual property names.
Example:
MEMBERPROPERTIES([Product].&[123], "Color", "Size")PROPERTIES
PROPERTIES(member_expression [, property_name1] [, property_name2, ...])Similar to MEMBERPROPERTIES, this function retrieves properties of a member. It's commonly used in conjunction with other functions to access member metadata.
Example:
PROPERTIES([Customer].[Country].&[USA], "Capital")ADDMEMBER
ADDMEMBER(set_expression, member_expression)Adds a specified member to a set. This is useful for creating derived sets that include specific members for calculation or display.
Example:
ADDMEMBER({[Date].[Calendar].&[202301]}, [Date].[Calendar].&[202302])Explore these functions to enhance your MDX queries and unlock the full potential of your multidimensional data models.