Instrucciones de origen · Vista previa de solo lectura
name
sql_to_dax
description
Guide for translating SQL aggregation expressions into DAX measures.
SKILL.md — SQL to DAX Metric Translation
Purpose
Translate analytical SQL aggregation expressions into equivalent DAX measures.
The goal is semantic equivalence, not syntactic similarity.
The generated DAX should:
Follow Power BI / Tabular best practices
Prefer iterator functions when row context is required
Use DIVIDE instead of / NULLIF(...,0)
Fully qualify columns using 'table'[column]
Use CALCULATE where filter context translation is required
Preserve aggregation semantics exactly
Avoid SQL constructs unsupported in DAX by rewriting logically
SQL Identifier Parsing Rules
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.
Identifier Normalization Rules
SQL Format
DAX Format
column_name
'table'[column_name]
table.column_name
'table'[column_name]
table.column name
'table'[column name]
Backtick Handling
SQL backticks must be removed during translation.
SQL
dim_product.`standard cost`
DAX
'dim_product'[standard cost]
Unqualified Column Resolution
If a column is referenced without a table qualifier:
SUM(SALES_AMOUNT)
The translator should:
Infer the table from model metadata if available
Prefer the primary fact table in the expression
Fully qualify the final DAX output
DAX
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:
Iterators (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.
Multiple columns participate in row-level arithmetic
Expressions exist inside aggregate functions
Mixed table references occur inside aggregation
SQL Aggregate
DAX Iterator
SUM(expr)
SUMX(table, expr)
AVG(expr)
AVERAGEX(table, expr)
MIN(expr)
MINX(table, expr)
MAX(expr)
MAXX(table, expr)
Safe Division
SQL NULLIF Pattern
SQL
SUM(sales) /NULLIF(SUM(cost), 0)
DAX
DIVIDE(
SUM('table'[sales]),
SUM('table'[cost])
)
Nested NULLIF
SQL
365/NULLIF(metric, 0)
DAX
DIVIDE(
365,
[metric]
)
Percentage Calculations
SQL
(metric / total) *100
DAX
DIVIDE(
[metric],
[total]
) * 100
ROUND Translation
SQL
ROUND(expression, 2)
DAX
ROUND(expression, 2)
DIV0 Translation
DIV0 means divide-by-zero-safe division.
SQL
DIV0(a, b)
DAX
DIVIDE(a, b)
Window Function Translation
Rolling Window SUM
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:
The window's ORDER BY column becomes the date column passed to
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.
When the inner expression is itself an aggregate (a non-standard but
common Snowflake/BigQuery pattern such as SUM(SUM(...)) OVER (...)),
the redundant outer aggregate is stripped and only the inner
aggregation body is preserved.
The inner aggregation body is translated using the normal aggregation
rules (including the additive distribution rule that turns
SUM(a - b) into (SUM(a) - SUM(b))).
SQL
SUM(metric)
OVER (
ORDERBY DATE_KEY
ROWSBETWEEN89 PRECEDING ANDCURRENTROW
)
DAX
CALCULATE(
[metric],
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>)).
Choose the fact table as the iterator table when possible
Iterator Table Selection from Relationships
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.
Example
Given a relationship: fact_sales (Many) → dim_product (One)
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.
Nested aggregates inside a window function are flattened — the redundant
outer aggregate is dropped and only the inner aggregate body is wrapped
in CALCULATE.
SQL
SUM(SUM(revenue)) OVER (
ORDERBY DATE_KEY
ROWSBETWEEN89 PRECEDING ANDCURRENTROW
)
DAX
CALCULATE(
SUM('fact_sales'[revenue]),
DATESINPERIOD(
'dim_date'[date_key],
MAX('dim_date'[date_key]),
-89,
DAY
)
)
