DAX Queries

For Data Analysts

This guide covers DAX query syntax supported by Olytix Core, including examples for common analytical patterns.

Query Structure

A DAX query consists of optional DEFINE statements followed by an EVALUATE statement:

[DEFINE
    MEASURE Table[MeasureName] = <expression>
    VAR VariableName = <expression>
]
EVALUATE <table expression>
[ORDER BY <expression> [ASC | DESC]]

EVALUATE Statement

The EVALUATE statement returns a table result. It is required in every DAX query.

Simple Table Query

Return all rows from a cube:

EVALUATE orders

With ORDER BY

Sort results by one or more columns:

EVALUATE orders
ORDER BY orders[order_date] DESC

SUMMARIZECOLUMNS

The primary function for analytical queries. Groups data by dimensions and calculates measures.

Basic Syntax

SUMMARIZECOLUMNS(
    <grouping columns>,
    [<filter tables>],
    "<measure name>", <measure expression>,
    ...
)

Example: Revenue by Region

EVALUATE
SUMMARIZECOLUMNS(
    orders[region],
    "Total Revenue", SUM(orders[amount]),
    "Order Count", COUNTROWS(orders)
)

REST API Request:

curl -X POST http://localhost:8000/api/v1/dax/execute \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "EVALUATE SUMMARIZECOLUMNS(orders[region], \"Total Revenue\", SUM(orders[amount]), \"Order Count\", COUNTROWS(orders))"
  }'

Response:

{
  "columns": [
    {"name": "region", "data_type": "String", "is_measure": false},
    {"name": "Total Revenue", "data_type": "Double", "is_measure": true},
    {"name": "Order Count", "data_type": "Integer", "is_measure": true}
  ],
  "data": [
    {"region": "NORTH", "Total Revenue": 125000.00, "Order Count": 1523},
    {"region": "SOUTH", "Total Revenue": 98500.00, "Order Count": 1187},
    {"region": "EAST", "Total Revenue": 112000.00, "Order Count": 1342},
    {"region": "WEST", "Total Revenue": 87500.00, "Order Count": 1056}
  ],
  "row_count": 4,
  "execution_time_ms": 52.3
}

Multiple Grouping Columns

EVALUATE
SUMMARIZECOLUMNS(
    orders[region],
    orders[product_category],
    "Revenue", SUM(orders[amount])
)
ORDER BY orders[region], orders[product_category]

With Inline Filters

Apply filters directly in SUMMARIZECOLUMNS:

EVALUATE
SUMMARIZECOLUMNS(
    orders[region],
    FILTER(ALL(orders[status]), orders[status] = "completed"),
    "Completed Revenue", SUM(orders[amount])
)

FILTER Function

Creates a filtered table based on a condition.

Basic Filtering

EVALUATE
FILTER(
    orders,
    orders[amount] > 1000
)

Multiple Conditions

EVALUATE
FILTER(
    orders,
    orders[status] = "completed" && orders[amount] >= 500
)

Combined with SUMMARIZECOLUMNS

EVALUATE
SUMMARIZECOLUMNS(
    orders[region],
    FILTER(orders, orders[amount] > 100),
    "High Value Orders", COUNTROWS(orders)
)

CALCULATE Function

Modifies the filter context for measure evaluation.

Apply Additional Filters

EVALUATE
SUMMARIZECOLUMNS(
    orders[region],
    "All Revenue", SUM(orders[amount]),
    "Completed Revenue", CALCULATE(
        SUM(orders[amount]),
        orders[status] = "completed"
    )
)

Remove Filters with ALL

EVALUATE
SUMMARIZECOLUMNS(
    orders[region],
    "Region Revenue", SUM(orders[amount]),
    "Total Revenue", CALCULATE(SUM(orders[amount]), ALL(orders[region]))
)

TOPN Function

Returns the top N rows based on an expression.

Top 10 Customers by Revenue

EVALUATE
TOPN(
    10,
    SUMMARIZECOLUMNS(
        orders[customer_id],
        "Revenue", SUM(orders[amount])
    ),
    [Revenue],
    DESC
)

Top Products per Category

EVALUATE
TOPN(
    5,
    SUMMARIZECOLUMNS(
        products[product_name],
        products[category],
        "Sales", SUM(orders[amount])
    ),
    [Sales]
)

ADDCOLUMNS and SELECTCOLUMNS

Add calculated columns to a table.

ADDCOLUMNS

Adds columns while keeping existing ones:

EVALUATE
ADDCOLUMNS(
    SUMMARIZECOLUMNS(
        orders[region],
        "Revenue", SUM(orders[amount])
    ),
    "Revenue Rank", RANKX(ALL(orders[region]), [Revenue])
)

SELECTCOLUMNS

Creates a table with only specified columns:

EVALUATE
SELECTCOLUMNS(
    orders,
    "Order ID", orders[order_id],
    "Customer", orders[customer_name],
    "Total", orders[amount]
)

DEFINE Statement

Define calculated measures and variables for use in the query.

Define Measures

DEFINE
    MEASURE orders[Total Revenue] = SUM(orders[amount])
    MEASURE orders[Avg Order Value] = AVERAGE(orders[amount])
EVALUATE
SUMMARIZECOLUMNS(
    orders[region],
    "Revenue", [Total Revenue],
    "AOV", [Avg Order Value]
)

Define Variables

DEFINE
    VAR CurrentYear = 2024
EVALUATE
FILTER(
    SUMMARIZECOLUMNS(
        orders[region],
        "Revenue", SUM(orders[amount])
    ),
    YEAR(orders[order_date]) = CurrentYear
)

Aggregation Functions

Function	Description	Example
SUM	Sum of values	`SUM(orders[amount])`
AVERAGE	Average of values	`AVERAGE(orders[amount])`
MIN	Minimum value	`MIN(orders[order_date])`
MAX	Maximum value	`MAX(orders[amount])`
COUNT	Count of non-blank values	`COUNT(orders[customer_id])`
COUNTROWS	Count of rows	`COUNTROWS(orders)`
DISTINCTCOUNT	Count of distinct values	`DISTINCTCOUNT(orders[customer_id])`
COUNTA	Count of non-empty values	`COUNTA(orders[notes])`
COUNTBLANK	Count of blank values	`COUNTBLANK(orders[discount])`

Iterator Functions

Iterator functions (X functions) evaluate an expression row-by-row:

Function	Description	Example
SUMX	Sum with row context	`SUMX(orders, orders[quantity] * orders[unit_price])`
AVERAGEX	Average with row context	`AVERAGEX(orders, orders[amount] / orders[quantity])`
MINX	Minimum with expression	`MINX(orders, orders[amount] - orders[discount])`
MAXX	Maximum with expression	`MAXX(orders, orders[amount] * 1.1)`
COUNTX	Conditional count	`COUNTX(orders, IF(orders[amount] > 100, 1, BLANK()))`

Example: Weighted Average

EVALUATE
SUMMARIZECOLUMNS(
    products[category],
    "Weighted Avg Price", SUMX(products, products[price] * products[units_sold]) / SUM(products[units_sold])
)

Filter Functions

Function	Description	Example
FILTER	Filter rows by condition	`FILTER(orders, orders[status] = "completed")`
ALL	Remove all filters	`ALL(orders)`
ALLEXCEPT	Remove all filters except specified	`ALLEXCEPT(orders, orders[region])`
VALUES	Distinct values	`VALUES(orders[region])`
DISTINCT	Distinct column values	`DISTINCT(orders[customer_id])`

Logical Functions

Function	Description	Example
IF	Conditional	`IF(orders[amount] > 1000, "High", "Low")`
AND	Logical AND	`AND(orders[status] = "completed", orders[amount] > 0)`
OR	Logical OR	`OR(orders[region] = "NORTH", orders[region] = "SOUTH")`
NOT	Logical NOT	`NOT(ISBLANK(orders[discount]))`
SWITCH	Multiple conditions	`SWITCH(orders[status], "new", 1, "processing", 2, 0)`

Date Functions

Function	Description	Example
YEAR	Extract year	`YEAR(orders[order_date])`
MONTH	Extract month	`MONTH(orders[order_date])`
DAY	Extract day	`DAY(orders[order_date])`
TODAY	Current date	`TODAY()`
NOW	Current date/time	`NOW()`
DATEDIFF	Difference between dates	`DATEDIFF(orders[order_date], TODAY(), DAY)`

Time-Based Analysis

EVALUATE
SUMMARIZECOLUMNS(
    YEAR(orders[order_date]),
    MONTH(orders[order_date]),
    "Monthly Revenue", SUM(orders[amount])
)
ORDER BY YEAR(orders[order_date]), MONTH(orders[order_date])

Query Validation

Validate DAX syntax before execution:

curl -X POST http://localhost:8000/api/v1/dax/validate \
  -H "Authorization: Bearer YOUR_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "EVALUATE SUMMARIZECOLUMNS(orders[region], \"Revenue\", SUM(orders[amount]))"
  }'

Valid Response:

{
  "valid": true,
  "errors": [],
  "warnings": []
}

Invalid Response:

{
  "valid": false,
  "errors": ["Syntax error: unexpected token at line 1, column 15"],
  "warnings": []
}

Error Handling

Common Errors

Error	Cause	Solution
Unknown table	Table/cube not found	Verify cube name in `/dax/cubes`
Unknown column	Column not in cube	Check available dimensions/measures
Syntax error	Invalid DAX syntax	Use `/dax/validate` to check syntax
Function not supported	Unsupported DAX function	Check supported functions list

Error Response Format

{
  "error": {
    "code": "DAX_SYNTAX_ERROR",
    "message": "Syntax error at line 1: unexpected token 'EVALUTE'",
    "suggestion": "Did you mean 'EVALUATE'?"
  }
}

Performance Tips

Use SUMMARIZECOLUMNS instead of SUMMARIZE for better performance
Limit result sets with TOPN or ORDER BY with a limit
Filter early in the query to reduce data volume
Avoid complex iterators when simpler aggregations work
Use pre-aggregations for frequently queried patterns

Query Structure​

EVALUATE Statement​

Simple Table Query​

With ORDER BY​

SUMMARIZECOLUMNS​

Basic Syntax​

Example: Revenue by Region​

Multiple Grouping Columns​

With Inline Filters​

FILTER Function​

Basic Filtering​

Multiple Conditions​

Combined with SUMMARIZECOLUMNS​

CALCULATE Function​

Apply Additional Filters​

Remove Filters with ALL​

TOPN Function​

Top 10 Customers by Revenue​

Top Products per Category​

ADDCOLUMNS and SELECTCOLUMNS​

ADDCOLUMNS​

SELECTCOLUMNS​

DEFINE Statement​

Define Measures​

Define Variables​

Aggregation Functions​

Iterator Functions​

Example: Weighted Average​

Filter Functions​

Logical Functions​

Date Functions​

Time-Based Analysis​

Query Validation​

Error Handling​

Common Errors​

Error Response Format​

Performance Tips​

Next Steps​