Scalar Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
Scalar functions return a single value (numeric, string, or boolean). They are used inside calculated member expressions.
SUM
Description: Returns the sum of a numeric expression evaluated over a set.
Syntax: SUM(Set_Expression [, Numeric_Expression])
Input: A set; optional numeric expression (defaults to current measure)
Output: Numeric total
Business Use Case: Calculate year-to-date sales totals across all months up to current context.
WITH MEMBER [Measures].[YTD Sales] AS SUM(
YTD([Date].[Calendar].CurrentMember), [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[YTD Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]AVG
Description: Returns the average of a numeric expression over a set. Empty cells are excluded by default. Syntax: AVG(Set_Expression [, Numeric_Expression])
Input: A set; optional numeric expression
Output: Numeric average
Business Use Case: Compute the average monthly sales across all months in a year to benchmark against individual months.
WITH MEMBER [Measures].[Avg Monthly Sales] AS AVG(
[Date].[Calendar].[Month].Members, [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Avg Monthly Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]MIN
Description: Returns the minimum value of a numeric expression over a set.
Syntax: MIN(Set_Expression [, Numeric_Expression])
Business Use Case: Find the lowest-performing month to identify seasonal troughs.
WITH MEMBER [Measures].[Min Monthly Sales] AS MIN(
[Date].[Calendar].[Month].Members, [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Min Monthly Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]MAX
Description: Returns the maximum value of a numeric expression over a set.
Syntax: MAX(Set_Expression [, Numeric_Expression])
Business Use Case: Identify the peak sales month for capacity planning.
WITH MEMBER [Measures].[Max Monthly Sales] AS MAX(
[Date].[Calendar].[Month].Members,
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Max Monthly Sales]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]COUNT
Description: Returns the number of members in a set.
Syntax:
COUNT(Set_Expression [, INCLUDEEMPTY | EXCLUDEEMPTY])
Set_Expression.Count
Variations:
counts all cells including empty ones
counts only non-empty cells (default)
Input: A set expression
Output: Integer count
Business Use Case: Count the number of products with any sales activity.
WITH MEMBER [Measures].[Products With Sales] AS COUNT(
Filter(
[Product].[Product Categories].[Product].Members, NOT ISEMPTY([Measures].[Internet Sales Amount])),
EXCLUDEEMPTY)
SELECT
[Measures].[Products With Sales] ON COLUMNS FROM [Adventure Works]Variation — using .Count property:
WITH MEMBER [Measures].[Total Subcategories] AS [Product].[Product Categories].[Subcategory].Members.Count
SELECT
[Measures].[Total Subcategories] ON COLUMNS FROM [Adventure Works]CASE
Description: Evaluates a series of conditions and returns the value for the first true condition. MDX supports both searched CASE and simple CASE.
Syntax — Searched CASE:
CASE
WHEN Condition1 THEN Value1
WHEN Condition2 THEN Value2
...
[ELSE DefaultValue]
ENDSyntax — Simple CASE:
CASE Expression
WHEN Value1 THEN Result1
WHEN Value2 THEN Result2
...
[ELSE DefaultValue]
ENDBusiness Use Case: Classify products into sales tiers for segmentation reporting.
WITH MEMBER [Measures].[Sales Tier] AS CASE
WHEN [Measures].[Internet Sales Amount] > 5000000 THEN "Platinum" WHEN [Measures].[Internet Sales Amount] > 1000000 THEN "Gold" WHEN [Measures].[Internet Sales Amount] > 100000 THEN "Silver" ELSE "Bronze"
END SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sales Tier]} ON COLUMNS,
[Product].[Product Categories].[Subcategory].Members ON ROWS FROM [Adventure Works]IIF (Scalar Form)
Description: Returns one of two scalar values based on a logical condition (inline if).
Syntax:
Input: A boolean condition and two values (numeric, string, or member)
Output: True_Value if condition is true; False_Value otherwise
Business Use Case: Prevent division-by-zero errors in ratio calculations.
WITH MEMBER [Measures].[Safe Margin] AS IIF([Measures].[Internet Sales Amount] = 0,
NULL,
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Safe Margin]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]Rank
Description: Returns the one-based rank of a member within a set based on a numeric expression.
Syntax: Rank(Member_Expression, Set_Expression [, Numeric_Expression])
Input: A member, the set to rank within, optional sort expression
Output: Integer rank (1 = best/first)
Business Use Case: Show each product's sales rank relative to all products.
WITH MEMBER [Measures].[Sales Rank] AS Rank(
[Product].[Product Categories].CurrentMember, Order(
[Product].[Product Categories].[Subcategory].Members, [Measures].[Internet Sales Amount],
BDESC),
[Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sales Rank]} ON COLUMNS,
[Product].[Product Categories].[Subcategory].Members ON ROWS FROM [Adventure Works]XIRR
Description: Calculates the Internal Rate of Return for a schedule of cash flows that is not necessarily periodic.
Syntax: XIRR(Set_Expression, CF_Expression, Date_Expression)
Input: A set representing periods, a cash flow measure, a date expression
Output: Annualized IRR as a decimal
Business Use Case: Evaluate the return on a product launch investment using irregular cash flows over time.
WITH MEMBER [Measures].[Investment IRR] AS XIRR(
[Date].[Calendar].[Month].Members, [Measures].[Cash Flow], CDate([Date].[Calendar].CurrentMember.Name))
SELECT
[Measures].[Investment IRR] ON COLUMNS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]Median
Description: Returns the median value of a numeric expression over a set.
Syntax: Median(Set_Expression [, Numeric_Expression])
Input: A set; optional numeric expression
Output: Median value
Business Use Case: Find the median monthly sales to identify outliers vs. typical performance.
WITH MEMBER [Measures].[Median Monthly Sales] AS Median(
[Date].[Calendar].[Month].Members, [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Median Monthly Sales]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].Members ON ROWS
FROM [Adventure Works]Percentile
Description: Returns the value at the specified percentile for a numeric expression over a set.
Syntax: Percentile(Set_Expression, Numeric_Expression, Percentile_Value)
Input: A set, numeric expression, percentile (0-100)
Output: Value at the specified percentile
Business Use Case: Find the 90th percentile sales value to identify high-performing outliers.
WITH MEMBER [Measures].[P90 Sales] AS Percentile(
[Product].[Product Categories].[Product].Members, [Measures].[Internet Sales Amount],
90)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[P90 Sales]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]STDEVP
Description: Returns the population standard deviation of a numeric expression over a set.
Syntax: STDEVP(Set_Expression [, Numeric_Expression])
Input: A set; optional numeric expression
Output: Standard deviation (population)
Business Use Case: Measure the volatility of monthly sales — high standard deviation indicates inconsistent performance.
WITH MEMBER [Measures].[Sales Volatility] AS STDEVP(
[Date].[Calendar].[Month].Members, [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Sales Volatility]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]Format
Description: Formats a numeric or date value as a string using a format expression.
Syntax: Format(Value_Expression, String_Expression)
Common Format Strings:
"Currency" — locale currency
"Percent" — percentage with 2 decimal places
"#,##0.00" — number with thousands separator
"$#,##0" — dollar format
"0.0%" — custom percentage
"yyyy-MM-dd" — date format
Business Use Case: Display profit margin as a formatted percentage string.
WITH MEMBER [Measures].[Formatted Margin] AS Format(
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount], "0.0%")
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Formatted Margin]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]POWER / POW
Description: Returns a number raised to a specified power.
Syntax:
POWER(Numeric_Expression, Power)
POW(Numeric_Expression, Power)
Input: Base number and exponent
Output: Numeric result
Business Use Case: Calculate compound growth factor over multiple periods.
WITH MEMBER [Measures].[Compound Growth] AS POWER(1.05, 5)
SELECT
[Measures].[Compound Growth] ON COLUMNS
FROM [Adventure Works]Variation — apply to measure:
WITH MEMBER [Measures].[Squared Sales Index] AS POW([Measures].[Internet Sales Amount] / 1000000, 2)
SELECT
{[Measures].[Internet Sales Amount],
[Measures].[Squared Sales Index]} ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]SQRT
Description: Returns the square root of a numeric expression.
Syntax: SQRT(Numeric_Expression)
Business Use Case: Normalize sales variance in statistical analysis.
WITH MEMBER [Measures].[Sales STDEV] AS STDEVP(
[Date].[Calendar].[Month].Members, [Measures].[Internet Sales Amount])
MEMBER [Measures].[Sqrt Sales STDEV] AS
SQRT([Measures].[Sales STDEV]) SELECT
[Measures].[Sqrt Sales STDEV] ON COLUMNS FROM [Adventure Works]ROUND
Description: Rounds a numeric value to a specified number of decimal places.
Syntax: ROUND(Numeric_Expression, Precision)
Business Use Case: Round profit margin to 2 decimal places for display.
WITH MEMBER [Measures].[Rounded Margin] AS ROUND(
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount] * 100, 2)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Rounded Margin]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]ROUNDUP
Description: Rounds a number up (away from zero) to a specified number of decimal places.
Syntax: ROUNDUP(Numeric_Expression, Precision)
WITH MEMBER [Measures].[Units Ceiling] AS ROUNDUP([Measures].[Internet Order Quantity] / 100, 0)
SELECT
{[Measures].[Internet Order Quantity], [Measures].[Units Ceiling]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]ROUNDDOWN
Description: Rounds a number down (toward zero) to a specified number of decimal places.
Syntax: ROUNDDOWN(Numeric_Expression, Precision)
WITH MEMBER [Measures].[Units Floor] AS ROUNDDOWN([Measures].[Internet Order Quantity] / 100, 0)
SELECT
{[Measures].[Internet Order Quantity], [Measures].[Units Floor]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]ABS
Description: Returns the absolute (non-negative) value of a numeric expression.
Syntax: ABS(Numeric_Expression)
Business Use Case: Calculate variance magnitude regardless of direction (over or under budget).
WITH MEMBER [Measures].[Sales Variance] AS [Measures].[Internet Sales Amount]
- ([Measures].[Internet Sales Amount], ParallelPeriod([Date].[Calendar].[Calendar Year], 1,
[Date].[Calendar].CurrentMember)) MEMBER [Measures].[Abs Variance] AS
ABS([Measures].[Sales Variance]) SELECT
{[Measures].[Sales Variance], [Measures].[Abs Variance]} ON COLUMNS,
[Date].[Calendar].[Month].Members ON ROWS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]LN
Description: Returns the natural logarithm (base e) of a number.
Syntax: LN(Numeric_Expression)
Business Use Case: Log-transform skewed sales data for statistical normalization.
WITH MEMBER [Measures].[Log Sales] AS LN([Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Log Sales]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]EXP
Description: Returns e (Euler's number, ~2.71828) raised to the power of a numeric expression.
Syntax:
Input: A numeric expression
Output: Exponential value
Business Use Case: Reverse a natural log transformation or model exponential growth.
WITH MEMBER [Measures].[Exp Sales] AS Exp([Measures].[Internet Sales Amount] / 100000)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Exp Sales]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]Decile
Description: Assigns each member a decile rank (1-10) based on its value relative to the full set.
Syntax: Decile(Set_Expression, Numeric_Expression)
Input: A set and a numeric expression for ranking
Output: Integer from 1 to 10 (decile bucket)
Business Use Case: Segment customers into 10 equal groups by purchase value for tiered marketing.
WITH MEMBER [Measures].[Customer Decile] AS Decile(
[Customer].[Customer Geography].[Customer].Members, [Measures].[Internet Sales Amount])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Customer Decile]} ON COLUMNS,
[Customer].[Customer Geography].[Customer].Members ON ROWS
FROM [Adventure Works]CurrentOrdinal
Description: Returns the one-based iteration index of the current member during a set iteration (useful in Generate).
Syntax: Set_Expression.CurrentOrdinal
Business Use Case: Number rows in a generated list.
WITH MEMBER [Measures].[Row Number] AS
[Product].[Product Categories].[Category].CurrentOrdinal
SELECT
{[Measures].[Row Number],
[Measures].[Internet Sales Amount]} ON COLUMNS, [Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]
ResponseSubset (Kyvos)
Description: Kyvos custom function. Returns a subset of a response/result set based on specified criteria.
Syntax: ResponseSubset(Set_Expression, Criteria_Expression)
Input: A set and filtering criteria
Output: Filtered subset
WITH MEMBER [Measures].[Filtered Sales] AS SUM(
ResponseSubset(
[Product].[Product Categories].[Product].Members, [Measures].[Internet Sales Amount] > 100000),
[Measures].[Internet Sales Amount])
SELECT
[Measures].[Filtered Sales] ON COLUMNS
FROM [Adventure Works]AllValue
Description: Returns the value of a measure at the "All" level of a specified dimension — the grand total regardless of current filter context.
Syntax: AllValue(Measure_Expression, Dimension_Expression)
Input: A measure and a dimension
Output: Grand total value for that measure on that dimension
Business Use Case: Calculate each category's percentage contribution to the total without a separate grand total query.
WITH MEMBER [Measures].[Pct of Total] AS [Measures].[Internet Sales Amount]
/ AllValue([Measures].[Internet Sales Amount], [Product].[Product Categories])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Pct of Total]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]ROLLUPCHILDREN
Description: Returns the rollup value of a parent member calculated using a specified unary operator applied to its children.
Syntax: ROLLUPCHILDREN(Member_Expression, String_Expression)
Unary Operators:
"+" — adds child to parent
"-" — subtracts child from parent
"*" — multiplies child with running total
"/" — divides
"~" — ignores child (excludes from rollup)
"|" — passes through child value
Input: A parent member and unary operator string Output: Calculated rollup value
Business Use Case: Calculate an income statement where expenses are subtracted from revenue.
WITH MEMBER [Measures].[Custom Rollup] AS ROLLUPCHILDREN(
[Product].[Product Categories].[All Products], "+")
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Custom Rollup]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]ISUDA (Kyvos)
Description: Kyvos-specific function. Returns whether the current member is a user-defined attribute member.
Syntax: ISUDA(Member_Expression)
Input: A member expression
Output: Boolean (TRUE/FALSE)
WITH MEMBER [Measures].[Is UDA Member] AS IIF(ISUDA([Product].[Product Categories].CurrentMember), 1, 0)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Is UDA Member]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]PROD
Description: Returns the product (multiplication) of a numeric expression over a set.
Syntax: PROD(Set_Expression [, Numeric_Expression])
Input: A set; optional numeric expression
Output: Product of all values
Business Use Case: Calculate compound multipliers (e.g., chained growth rates).
WITH MEMBER [Measures].[Compound Factor] AS PROD(
[Date].[Calendar].[Month].Members,
1 + ([Measures].[Internet Sales Amount]
- ([Measures].[Internet Sales Amount],
[Date].[Calendar].CurrentMember.PrevMember))
/ ([Measures].[Internet Sales Amount], [Date].[Calendar].CurrentMember.PrevMember))
SELECT
[Measures].[Compound Factor] ON COLUMNS FROM [Adventure Works]
WHERE [Date].[Calendar].[Calendar Year].[2012]DIVIDE
Description: Performs division with a safe fallback for division by zero.
Syntax: DIVIDE(Numerator_Expression, Denominator_Expression [, Default_Value])
Input: Numerator, denominator, optional default when denominator is zero
Output: Quotient, or default value if denominator is zero
Business Use Case: Calculate profit margin safely without explicit IIF zero-check.
WITH MEMBER [Measures].[Safe Margin] AS DIVIDE(
[Measures].[Internet Sales Amount] - [Measures].[Total Product Cost], [Measures].[Internet Sales Amount],
0)
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Safe Margin]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]