Overview

Measures are the core quantitative components of semantic metrics. They define what data should be quantified and how it should be calculated. Each measure can have its own calculation logic, formatting rules, and data source specifications.

Diagram showing how measures fit into the metric architecture

Use Cases

Measures are used for:

• Revenue Analysis: Track sales, revenue, profit margins
• User Metrics: Count active users, sessions, conversions
• Performance KPIs: Response times, throughput, error rates
• Business Intelligence: Growth rates, market share, customer lifetime value

Syntax

Type: SemanticMeasure

List of quantitative measurements included in this metric. Each measure defines what data should be quantified and how it should be calculated.

{
    "measures": [
        {
            "name": "total_revenue",
            "description": "Sum of all revenue",
            "type": "sum",
            "query": "revenue_amount",
            "table": "orders",
            "alias": "revenue",
            "primary_key": "order_id",
            "formatting": [
                {
                    "name": "currency_format",
                    "type": "format",
                    "format_type": "currency",
                    "format_string": "$%.2f"
                }
            ]
        }
    ]
}

Parameters

name

Type: str
Required: Yes

Unique identifier name for this measure. Used to reference the measure in queries and API calls.

{
    "name": "total_revenue"
}

description

Type: Optional[str]
Default: None
Required: No

Human-readable explanation of what this measure represents. This helps other developers understand the purpose and calculation logic.

{
    "description": "Sum of all revenue from completed orders"
}

type

Type: SemanticMeasureType
Required: Yes

Calculation type to be applied. This determines how the data will be aggregated and calculated.

Available Values:

• Basic Types: string, number, count, count_distinct
• Aggregations: sum, avg, min, max, percent
• Data Types: boolean, date, timestamp, duration

{
    "type": "sum"
}

formatting

Type: Optional[List[OutputFormat]]
Default: None
Required: No

Array of OutputFormat objects for result transformation. This allows you to format the output for display purposes.

{
    "formatting": [
        {
            "name": "currency_format",
            "type": "format",
            "format_type": "currency",
            "format_string": "$%.2f"
        }
    ]
}

alias

Type: Optional[str]
Default: None
Required: No

Alternative name to use in queries and results. Useful for creating more readable column names in the output.

{
    "alias": "revenue"
}

query

Type: Optional[str]
Default: None
Required: No

Column name or SQL expression that defines this measure. Used in query generation to specify the data field or calculation for this measure.

{
    "query": "revenue_amount * exchange_rate"
}

table

Type: Optional[str]
Default: None
Required: No

Source table or view where this measure's data resides. Specifies the primary data source for the measure.

{
    "table": "orders"
}

primary_key

Type: Optional[str]
Default: None
Required: No

Primary key column identifier for the table. Used for deduplication and join optimization.

{
    "primary_key": "order_id"
}

conditional

Type: bool
Default: False
Required: No

Boolean flag indicating whether to use conditional logic (CASE WHEN statements) instead of the simple query field. When set to true, the conditions field should be provided.

{
    "conditional": true
}

conditions

Type: Optional[Condition]
Default: None
Required: No

A Condition object defining CASE WHEN logic with multiple clauses. Used when conditional=True to create complex aggregations based on conditions.

See Conditional Logic Architecture for complete details on building conditional statements.

{
    "conditional": true,
    "conditions": {
        "when_clauses": [
            {
                "field": {
                    "column": "status",
                    "table": "attendance"
                },
                "operator": "IN",
                "compare_values": ["present", "attended"],
                "then_return": 1
            }
        ],
        "else_return": 0
    }
}

Conditional Logic

Measures support conditional logic through the conditional flag and conditions field. This allows you to create CASE WHEN statements for complex aggregations.

When to Use Conditional Logic

Use conditional logic when you need:

• Multiple WHEN clauses with different return values
• Data transformations (LOWER, COALESCE, EXTRACT, etc.) before comparison
• Dynamic categorization based on values
• Complex business logic that can't be expressed with simple queries

Simple Example: Attendance Percentage

Calculate what percentage of attendance records are marked as present:

{
    "name": "Attendance %",
    "description": "Percentage of marked attendance",
    "type": "avg",
    "conditional": true,
    "conditions": {
        "when_clauses": [
            {
                "field": {
                    "column": "status",
                    "table": "attendance",
                    "transforms": [
                        {
                            "function": "COALESCE",
                            "params": {"fallback": ""}
                        },
                        {
                            "function": "LOWER"
                        }
                    ]
                },
                "operator": "IN",
                "compare_values": ["present", "attended", "yes", "y", "p"],
                "then_return": 1
            }
        ],
        "else_return": 0
    }
}

Generated SQL:

AVG(
  CASE
    WHEN LOWER(COALESCE(attendance.status, '')) IN ('present', 'attended', 'yes', 'y', 'p') THEN 1
    ELSE 0
  END
) AS "Attendance %"

Choosing Between Simple and Conditional

Use conditional=false (simple query) when:

• Working with a single column
• No CASE WHEN logic needed
• Simple aggregation

Use conditional=true (conditions) when:

• Need multiple WHEN clauses
• Require data transformations
• Building complex business logic

For complete details on building conditional statements, see the Conditional Logic Architecture.

Patterns

Revenue Metrics

{
    "measures": [
        {
            "name": "total_revenue",
            "type": "sum",
            "query": "amount",
            "table": "orders",
            "formatting": [
                {
                    "name": "currency",
                    "type": "format",
                    "format_type": "currency",
                    "format_string": "$%.2f"
                }
            ]
        },
        {
            "name": "average_order_value",
            "type": "avg",
            "query": "amount",
            "table": "orders"
        }
    ]
}

User Metrics

{
    "measures": [
        {
            "name": "active_users",
            "type": "count_distinct",
            "query": "user_id",
            "table": "user_sessions"
        },
        {
            "name": "total_sessions",
            "type": "count",
            "query": "session_id",
            "table": "user_sessions"
        }
    ]
}

Performance Metrics

{
    "measures": [
        {
            "name": "response_time_avg",
            "type": "avg",
            "query": "response_time_ms",
            "table": "api_logs",
            "formatting": [
                {
                    "name": "time_format",
                    "type": "format",
                    "format_type": "custom",
                    "format_string": "%.2f ms"
                }
            ]
        }
    ]
}

Best Practices

1. Use Descriptive Names: Choose clear, descriptive names that indicate what the measure represents
2. Add Descriptions: Always include descriptions to help other developers understand the measure
3. Consider Formatting: Use appropriate formatting for different data types (currency, percentages, etc.)
4. Optimize Queries: Use specific column names rather than * for better performance
5. Handle Nulls: Consider how null values should be handled in your calculations

Video Tutorial

Video: Step-by-step guide to configuring measures in the Cortex dashboard

Related Topics

• Dimensions Configuration - Learn how to group and categorize your measures
• Joins Configuration - Advanced formatting options for measure results
• Measures Configuration - Complete metric setup guide