Logical Functions
This documentation is for Kyvos 2026.6.x. To view documentation for other Kyvos versions, click here. |
|---|
Logical functions return boolean values and are used in conditions within IIF, Filter, and CASE expressions.
IIF (Logical Form)
Description: Evaluates a condition and returns one of two values (inline if-then-else). Works for scalar, member, and set return types.
Syntax: IIF(Logical_Expression, True_Return, False_Return)
Input: A boolean condition and two return expressions
Output: True_Return if condition is TRUE; False_Return if FALSE
Business Use Case: Prevent divide-by-zero; classify members conditionally.
WITH MEMBER [Measures].[Margin %] AS IIF([Measures].[Internet Sales Amount] = 0,
NULL,
([Measures].[Internet Sales Amount] - [Measures].[Total Product Cost])
/ [Measures].[Internet Sales Amount]), FORMAT_STRING = "Percent"
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Margin %]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS FROM [Adventure Works]IS
Description: Tests whether two members or objects are the same.
Syntax: Member_Expression1 IS Member_Expression2
Input: Two member expressions
Output: Boolean (TRUE if same member)
Business Use Case: Check if the current member is a specific member (e.g., apply special logic only for Bikes).
WITH MEMBER [Measures].[Is Bikes] AS IIF([Product].[Product Categories].CurrentMember
IS [Product].[Product Categories].[Category].[Bikes], "Yes", "No")
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Is Bikes]} ON COLUMNS,
[Product].[Product Categories].[Category].Members ON ROWS
FROM [Adventure Works]ISEMPTY
Description: Returns TRUE if a value expression evaluates to the empty cell value.
Syntax: ISEMPTY(Value_Expression)
Input: Any value expression
Output: Boolean
Business Use Case: Only show sales change where data exists in both periods.
WITH MEMBER [Measures].[Prior Year] AS ([Measures].[Internet Sales Amount],
ParallelPeriod([Date].[Calendar].[Calendar Year], 1, [Date].[Calendar].CurrentMember))
MEMBER [Measures].[YoY Change] AS IIF(ISEMPTY([Measures].[Prior Year]),
NULL,
[Measures].[Internet Sales Amount] - [Measures].[Prior Year])
SELECT
{[Measures].[Internet Sales Amount], [Measures].[Prior Year], [Measures].[YoY Change]} ON COLUMNS,
[Date].[Calendar].[Calendar Year].Members ON ROWS FROM [Adventure Works]AND
Description: Logical AND — TRUE if both conditions are TRUE.
Syntax: Condition1 AND Condition2
Business Use Case: Filter products that have both high sales AND high order volume.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Filter(
[Product].[Product Categories].[Subcategory].Members, [Measures].[Internet Sales Amount] > 2000000
AND [Measures].[Internet Order Quantity] > 500) ON ROWS
FROM [Adventure Works]OR
Description: Logical OR — TRUE if at least one condition is TRUE.
Syntax: Condition1 OR Condition2
Business Use Case: Show subcategories that are either in Bikes OR have sales over $3M.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Filter(
[Product].[Product Categories].[Subcategory].Members, [Product].[Product Categories].CurrentMember.Parent.Name = "Bikes" OR [Measures].[Internet Sales Amount] > 3000000) ON ROWS
FROM [Adventure Works]XOR
Description: Logical exclusive OR — TRUE if exactly one of the two conditions is TRUE (but not both).
Syntax: Condition1 XOR Condition2
Business Use Case: Find products where exactly one of two sales thresholds is met.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Filter(
[Product].[Product Categories].[Subcategory].Members, ([Measures].[Internet Sales Amount] > 2000000)
XOR ([Measures].[Internet Order Quantity] > 1000)) ON ROWS FROM [Adventure Works]NOT
Description: Logical negation — reverses the boolean value of an expression.
Syntax: NOT Logical_Expression
Business Use Case: Exclude Bikes category from an analysis.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Filter(
[Product].[Product Categories].[Category].Members,
NOT [Product].[Product Categories].CurrentMember.Name = "Bikes") ON ROWS FROM [Adventure Works]Relational Operators
Description: Standard comparison operators for numeric and string comparisons.
Operator | Meaning | Example |
= | Equal | [Sales] = 0 |
<> | Not equal | [Member] <> [All] |
> | Greater than | [Sales] > 1000000 |
>= | Greater than or equal | [Sales] >= 500000 |
< | Less than | [Sales] < 100 |
<= | Less than or equal | [Rank] <= 10 |
-- Combined relational operators SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Filter(
[Product].[Product Categories].[Subcategory].Members, [Measures].[Internet Sales Amount] >= 1000000
AND [Measures].[Internet Sales Amount] <= 5000000) ON ROWS
FROM [Adventure Works]IsAncestor
Description: Returns TRUE if the first member is an ancestor of the second member.
Syntax: IsAncestor(Member_Expression1, Member_Expression2)
Input: Two members in the same hierarchy
Output: Boolean
Business Use Case: Filter to show only members that are descendants of Bikes.
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS, Filter(
[Product].[Product Categories].Members, IsAncestor(
[Product].[Product Categories].[Category].[Bikes], [Product].[Product Categories].CurrentMember)
OR [Product].[Product Categories].CurrentMember
IS [Product].[Product Categories].[Category].[Bikes]) ON ROWS FROM [Adventure Works]IsGeneration
Description: Returns TRUE if a member is at the specified generation number.
Syntax: IsGeneration(Member_Expression, Generation_Number)
Input: A member and a generation integer (1 = root, higher = deeper)
Output: Boolean
Business Use Case: Apply different formatting logic to different hierarchy levels.
WITH MEMBER [Measures].[Hierarchy Depth] AS CASE
WHEN IsGeneration([Product].[Product Categories].CurrentMember, 1) THEN
"All"
WHEN IsGeneration([Product].[Product Categories].CurrentMember, 2) THEN "Category"
WHEN IsGeneration([Product].[Product Categories].CurrentMember, 3) THEN "Subcategory"
ELSE "Product"
END SELECT
{[Measures].[Internet Sales Amount], [Measures].[Hierarchy Depth]} ON COLUMNS,
Hierarchize([Product].[Product Categories].Members) ON ROWS FROM [Adventure Works]