with one click
sql-to-dax
Guide for translating SQL aggregation expressions into DAX measures.
Menu
Guide for translating SQL aggregation expressions into DAX measures.
| name | sql_to_dax |
| description | Guide for translating SQL aggregation expressions into DAX measures. |
Translate analytical SQL aggregation expressions into equivalent DAX measures.
The goal is semantic equivalence, not syntactic similarity.
The generated DAX should:
/ NULLIF(...,0)'table'[column]The SQL source may reference columns using any of the following formats:
column_name
table.column_name
table.`column name`
The translator must normalize all forms into valid DAX column references.
| SQL Format | DAX Format |
|---|---|
| column_name | 'table'[column_name] |
| table.column_name | 'table'[column_name] |
table.column name | 'table'[column name] |
SQL backticks must be removed during translation.
dim_product.`standard cost`
'dim_product'[standard cost]
If a column is referenced without a table qualifier:
SUM(SALES_AMOUNT)
The translator should:
SUM('fact_sales'[SALES_AMOUNT])
Important — bare identifiers inside scalar aggregates must NOT become iterators.
When a SQL aggregate wraps a single bare identifier (e.g.
SUM(ORIGINAL_SALES_AMOUNT),SUM(COST_OF_GOODS_SOLD)) and that identifier is not declared as a column in the model metadata, the translator must still emit the scalar aggregation form against the default/owning table:SUM('fact_returns'[ORIGINAL_SALES_AMOUNT])It must not fall through to the iterator form:
SUMX('fact_returns', ORIGINAL_SALES_AMOUNT) -- INVALIDIterators (
SUMX,AVERAGEX, etc.) are reserved for cases where the aggregate argument contains arithmetic or references multiple columns. A single bare token is always a scalar aggregation.
Expressions may mix styles.
SUM(fact_sales.`sales amount` - DISCOUNT_AMOUNT)
SUMX(
'fact_sales',
'fact_sales'[sales amount] -
'fact_sales'[DISCOUNT_AMOUNT]
)
SUM(column)
SUM('table'[column])
AVG(column)
AVERAGE('table'[column])
COUNT(DISTINCT column)
DISTINCTCOUNT('table'[column])
If arithmetic occurs inside SUM/AVG/etc., use iterator functions.
SUM(price * quantity)
SUMX(
'table',
'table'[price] * 'table'[quantity]
)
SUM(revenue - discount)
SUMX(
'table',
'table'[revenue] - 'table'[discount]
)
AVG(quantity * cost)
AVERAGEX(
'table',
'table'[quantity] * 'table'[cost]
)
Use iterator functions when:
| SQL Aggregate | DAX Iterator |
|---|---|
| SUM(expr) | SUMX(table, expr) |
| AVG(expr) | AVERAGEX(table, expr) |
| MIN(expr) | MINX(table, expr) |
| MAX(expr) | MAXX(table, expr) |
SUM(sales) / NULLIF(SUM(cost), 0)
DIVIDE(
SUM('table'[sales]),
SUM('table'[cost])
)
365 / NULLIF(metric, 0)
DIVIDE(
365,
[metric]
)
(metric / total) * 100
DIVIDE(
[metric],
[total]
) * 100
ROUND(expression, 2)
ROUND(expression, 2)
DIV0 means divide-by-zero-safe division.
DIV0(a, b)
DIVIDE(a, b)
Translate <agg>(<inner>) OVER (ORDER BY <col> ROWS BETWEEN N PRECEDING AND CURRENT ROW)
into a CALCULATE wrapping the inner aggregation with a DATESINPERIOD
filter over the ORDER BY column.
Rules:
DATESINPERIOD. It is resolved through the column map and fully
qualified as 'table'[column].MAX(<order_col>) is used as the anchor date.ROWS BETWEEN N PRECEDING AND CURRENT ROW becomes -N, DAY. The skill
preserves the literal N from the SQL (e.g. 89 PRECEDING → -89, DAY)
rather than rounding up to the inclusive day count.SUM(SUM(...)) OVER (...)),
the redundant outer aggregate is stripped and only the inner
aggregation body is preserved.SUM(a - b) into (SUM(a) - SUM(b))).SUM(metric)
OVER (
ORDER BY DATE_KEY
ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
)
CALCULATE(
[metric],
DATESINPERIOD(
'dim_date'[date_key],
MAX('dim_date'[date_key]),
-89,
DAY
)
)
SUM(
SUM(EXTENDED_AMOUNT - DISCOUNT_AMOUNT)
) OVER (
ORDER BY DATE_KEY
ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
)
CALCULATE(
(
SUM('fact_sales'[sales_amount])
- SUM('fact_sales'[discount_amount])
),
DATESINPERIOD(
'dim_date'[date_key],
MAX('dim_date'[date_key]),
-89,
DAY
)
)
Equivalently (when distribution is not applied), the inner body may be emitted in iterator form:
CALCULATE(
SUMX(
'fact_sales',
'fact_sales'[sales_amount] - 'fact_sales'[discount_amount]
),
DATESINPERIOD(
'dim_date'[date_key],
MAX('dim_date'[date_key]),
-89,
DAY
)
)
An unbounded window (OVER () with no PARTITION BY, ORDER BY, or frame)
ignores the current filter context entirely. Translate it to the
iterator form of the aggregate over ALL(<table>) — not to
CALCULATE(<agg>, ALL(<table>)).
| SQL aggregate | DAX |
|---|---|
SUM(col) OVER () | SUMX(ALL('table'), 'table'[col]) |
AVG(col) OVER () | AVERAGEX(ALL('table'), 'table'[col]) |
MIN(col) OVER () | MINX(ALL('table'), 'table'[col]) |
MAX(col) OVER () | MAXX(ALL('table'), 'table'[col]) |
COUNT(col) OVER () | COUNTX(ALL('table'), 'table'[col]) |
COUNT(*) OVER () | COUNTROWS(ALL('table')) |
COUNT(DISTINCT col) OVER () | CALCULATE(DISTINCTCOUNT('table'[col]), ALL('table')) |
MAX(cutoff) OVER ()
MAXX(ALL('table'), 'table'[cutoff])
SUM(amount) OVER ()
SUMX(ALL('table'), 'table'[amount])
COUNT(DISTINCT CASE
WHEN order_count > 1
THEN customer_key
END)
CALCULATE(
DISTINCTCOUNT('table'[customer_key]),
'table'[order_count] > 1
)
When expressions reference multiple tables:
When an X-function (SUMX, AVERAGEX, COUNTX, MINX, MAXX) is needed and
the inner expression references two tables that participate in a relationship,
choose the iterator table as the "from" side of that relationship
(typically the many / fact side). Wrap any column reference to the
"to" side (typically the one / dimension side) in RELATED(...).
This rule is independent of the table that the measure is defined on — what matters is which table is on the many side of the relationship linking the two referenced tables.
Given a relationship: fact_sales (Many) → dim_product (One)
SUM(dim_product.standard_cost * fact_sales.quantity_sold)
SUMX(
'fact_sales',
RELATED('dim_product'[standard_cost]) *
'fact_sales'[quantity_sold]
)
Even when this measure is authored on the dim_product table, the iterator
table is still fact_sales because fact_sales is the "from" side of the
relationship.
SUM(dim_product.standard_cost * fact_sales.quantity)
SUMX(
'fact_sales',
RELATED('dim_product'[standard_cost]) *
'fact_sales'[quantity]
)
SUM(
(EXTENDED_AMOUNT - DISCOUNT_AMOUNT)
- (STANDARD_COST * QUANTITY)
)
SUMX(
'fact_sales',
('fact_sales'[EXTENDED_AMOUNT] - 'fact_sales'[DISCOUNT_AMOUNT])
-
(
RELATED('dim_product'[STANDARD_COST]) *
'fact_sales'[QUANTITY]
)
)
SUM(revenue - cost)
SUMX(
'fact',
'fact'[revenue] - 'fact'[cost]
)
SUM(profit)
/
NULLIF(SUM(revenue), 0)
DIVIDE(
[Profit],
[Revenue]
)
SUM(return_amount)
/
NULLIF(SUM(original_sales_amount), 0)
DIVIDE(
SUM('fact_returns'[return_amount]),
SUM('fact_sales'[original_sales_amount])
)
Preferred:
'fact_sales'[sales_amount]
Avoid:
[sales_amount]
When SQL implies dimension lookup:
dim_product.standard_cost
inside fact aggregation becomes:
RELATED('dim_product'[standard_cost])
Use X-iterators when:
Nested aggregates inside a window function are flattened — the redundant
outer aggregate is dropped and only the inner aggregate body is wrapped
in CALCULATE.
SUM(SUM(revenue)) OVER (
ORDER BY DATE_KEY
ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
)
CALCULATE(
SUM('fact_sales'[revenue]),
DATESINPERIOD(
'dim_date'[date_key],
MAX('dim_date'[date_key]),
-89,
DAY
)
)
| SQL | DAX |
|---|---|
| SUM(col) | SUM(table[col]) |
| AVG(col) | AVERAGE(table[col]) |
| COUNT(DISTINCT col) | DISTINCTCOUNT(table[col]) |
| NULLIF(x,0) | DIVIDE(... ) |
| ROUND(x,n) | ROUND(x,n) |
| CASE WHEN | CALCULATE/FILTER |
| OVER(...) | CALCULATE + time intelligence |
| SUM(a*b) | SUMX(table,a*b) |
SUM(store_sales.ss_sales_price * store_sales.ss_quantity)
SUMX(
'store_sales',
'store_sales'[ss_sales_price] *
'store_sales'[ss_quantity]
)
SUM(ATTRIBUTED_REVENUE)
/
NULLIF(SUM(SPEND_AMOUNT), 0)
DIVIDE(
SUM('fact_marketing'[ATTRIBUTED_REVENUE]),
SUM('fact_marketing'[SPEND_AMOUNT])
)
(
COUNT(DISTINCT CASE
WHEN CUSTOMER_ORDER_COUNT > 1
THEN CUSTOMER_KEY
END)
/
NULLIF(COUNT(DISTINCT CUSTOMER_KEY), 0)
) * 100
DIVIDE(
CALCULATE(
DISTINCTCOUNT('customer'[CUSTOMER_KEY]),
'customer'[CUSTOMER_ORDER_COUNT] > 1
),
DISTINCTCOUNT('customer'[CUSTOMER_KEY])
) * 100
Generated DAX must:
/SQL is row-set based.
DAX is filter-context based.
Correct translation often requires:
Do not attempt direct token replacement.
Guide for the visual style, structure, and shared building blocks used by Semantic Link Labs interactive UI tools (HTML widgets and anywidget-based widgets). Use this when adding a new interactive UI, modifying an existing one, or adding shared visual components.
Guide for building documentation and validating docstrings. Use this when asked to build docs, check docstrings, or validate documentation.
Guide for running code style linters and formatters. Use this when asked to check code style, run linters, or fix formatting issues.
Guide for adding new functions to the library. Use this when implementing new API wrappers or utility functions.
Guide for working with Direct Lake semantic models. Use this when implementing Direct Lake-related features or troubleshooting.
Guide for searching and exploring external GitHub repositories using the gh CLI. Use this when you need reference implementations, patterns, or code examples from open-source projects to help complete your task.