Mod

The Mod function in MDX returns the remainder of a division operation. It helps calculate residuals or apply conditions based on divisibility.

Syntax

Mod(Numeric_Expression, Integer)

Arguments

Numeric_Expression	MDX expression that returns a numeric value (the dividend)
Integer	MDX expression that returns an integer value (the divisor). This value must not be zero.

Examples

Determine if an issue key is even or odd

The following example checks if a row in the report is odd or even by using the remainder when divided by 2. This can be used in scenarios to highlight the report rows. This formula uses Rank to identify the row index in the report first and then uses it to set Even or Odd. Then, using the cell formatting, the color can be added to the Even or Odd row.

CASE
  WHEN
   Mod(
    Rank(
      CurrentTuple(VisibleRowsSet()).Item(0),
      Generate(
        VisibleRowsSet(),
      CurrentTuple(VisibleRowsSet()).Item(0)
      )
    ),
    2) = 0
  THEN "Even"
  ELSE "Odd"
END

Assign the Groups based on the remainder

The following example applies a Group based on the Issue Key remainder when divided by 3.

CASE 
  WHEN Mod(Val(ExtractString([Issue].CurrentHierarchyMember.Key, '.*-(\d+)', 1)), 3) = 0 THEN "Group 1"
  WHEN Mod(Val(ExtractString([Issue].CurrentHierarchyMember.Key, '.*-(\d+)', 1)), 3) = 1 THEN "Group 2"
  ELSE "Group 3"
END

Assign the discounts based on the remainder

The following example applies a discount % based on the Unit Sales remainder when divided by 4.

CASE 
  WHEN Mod([Measures].[Unit Sales], 4) = 0 THEN 10 -- 10% discount
  WHEN Mod([Measures].[Unit Sales], 4) = 1 THEN 5  -- 5% discount
  WHEN Mod([Measures].[Unit Sales], 4) = 2 THEN 15 -- 15% discount
  ELSE 20 -- 20% discount for remainder 3
END

Determine if the Year is Leap Year

The following example determines whether the year is a Leap Year. For this calculation to work, the Time dimension should be selected in the Rows, and the Hierarchy Level Year should be selected.

CASE 
  WHEN (Mod(Cast([Time].CurrentMember.Name AS Numeric), 4) = 0
  AND Mod(Cast([Time].CurrentMember.Name AS Numeric), 100) <> 0) 
      OR (Mod(Cast([Time].CurrentMember.Name AS Numeric), 400) = 0) 
  THEN "Leap Year"
  ELSE "Not a Leap Year"
END

Mod