MDX Functions - Boolean

Boolean functions in MDX (Multidimensional Expressions) are used to evaluate logical expressions and return either TRUE or FALSE. These functions are crucial for conditional logic, filtering, and controlling the flow of MDX queries.

Boolean Functions

• AND AND (Boolean_Expression1, Boolean_Expression2, ...)

  Returns TRUE if all the arguments evaluate to TRUE. If any argument evaluates to FALSE, the AND function returns FALSE. The function can take multiple arguments.

  Returns: Boolean
• OR OR (Boolean_Expression1, Boolean_Expression2, ...)

  Returns TRUE if at least one argument evaluates to TRUE. If all arguments evaluate to FALSE, the OR function returns FALSE. The function can take multiple arguments.

  Returns: Boolean
• NOT NOT (Boolean_Expression)

  Reverses the logical state of its Boolean argument. Returns TRUE if the argument is FALSE, and FALSE if the argument is TRUE.

  Returns: Boolean
• XOR XOR (Boolean_Expression1, Boolean_Expression2)

  Returns TRUE if an odd number of arguments evaluate to TRUE. Returns FALSE if an even number of arguments evaluate to TRUE. For two arguments, it returns TRUE if one argument is TRUE and the other is FALSE.

  Returns: Boolean
• IIF IIF (Boolean_Expression, Value_if_True, Value_if_False)

  Evaluates a Boolean expression and returns one of two values based on the result. The Value_if_True is returned if the Boolean_Expression is TRUE, and the Value_if_False is returned if it is FALSE.

  Returns: The data type of Value_if_True and Value_if_False.
  Note: The IIF function is very useful for creating conditional logic within MDX queries, such as displaying specific text or values based on certain criteria.
• ISLeaf ISLeaf(Member_Expression)

  Returns TRUE if the specified member is a leaf member in its hierarchy. A leaf member has no children.

  Returns: Boolean
• ISAncestor ISAncestor(Member_Expression1, Member_Expression2)

  Returns TRUE if Member_Expression1 is an ancestor of Member_Expression2. An ancestor includes the member itself.

  Returns: Boolean
• ISChild ISChild(Member_Expression1, Member_Expression2)

  Returns TRUE if Member_Expression1 is a direct child of Member_Expression2.

  Returns: Boolean

Usage Examples

Here are a few examples demonstrating the use of boolean functions:

Example 1: Using IIF for conditional display

SELECT
    [Measures].[Sales Amount] ON COLUMNS,
    {[Product].[Category].&[Bikes], [Product].[Category].&[Clothing]} ON ROWS
FROM
    [Adventure Works]
WHERE
    IIF(
        [Measures].[Sales Amount] > 100000,
        "High Sales",
        "Regular Sales"
    )
            

Example 2: Filtering members based on a condition

SELECT
    [Measures].[Internet Sales Amount] ON COLUMNS
FROM
    [Adventure Works]
WHERE
    FILTER(
        [Date].[Calendar Year].MEMBERS,
        NOT(ISLeaf([Date].[Calendar Year].CurrentMember))
    )
            

These boolean functions are fundamental building blocks for creating dynamic and sophisticated MDX queries, enabling precise data analysis and reporting.