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

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:

1. Infer the table from model metadata if available
2. Prefer the primary fact table in the expression
3. 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(ORIGINALSALESAMOUNT),
SUM(COSTOFGOODS_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:

```dax
SUM('factreturns'[ORIGINALSALES_AMOUNT])
```

It must not fall through to the iterator form:

```dax
SUMX('factreturns', ORIGINALSALES_AMOUNT) -- INVALID
```

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.

Mixed Identifier Formats

Expressions may mix styles.

SQL

SUM(fact_sales.`sales amount` - DISCOUNT_AMOUNT)

DAX

SUMX(
    'fact_sales',
    'fact_sales'[sales amount] -
    'fact_sales'[DISCOUNT_AMOUNT]
)

Core Translation Rules

1. Aggregate Functions

SQL

SUM(column)

DAX

SUM('table'[column])

SQL

AVG(column)

DAX

AVERAGE('table'[column])

SQL

COUNT(DISTINCT column)

DAX

DISTINCTCOUNT('table'[column])

2. Arithmetic Inside Aggregations

If arithmetic occurs inside SUM/AVG/etc., use iterator functions.

SQL

SUM(price * quantity)

DAX

SUMX(
    'table',
    'table'[price] * 'table'[quantity]
)

SQL

SUM(revenue - discount)

DAX

SUMX(
    'table',
    'table'[revenue] - 'table'[discount]
)

SQL

AVG(quantity * cost)

DAX

AVERAGEX(
    'table',
    'table'[quantity] * 'table'[cost]
)

Iterator Function Rules

Use iterator functions when:

• Multiple columns participate in row-level arithmetic
• Expressions exist inside aggregate functions
• Mixed table references occur inside aggregation

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 (
    ORDER BY DATE_KEY
    ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
)

DAX

CALCULATE(
    [metric],
    DATESINPERIOD(
        'dim_date'[date_key],
        MAX('dim_date'[date_key]),
        -89,
        DAY
    )
)

Rolling Window over a Nested Aggregate

SQL

SUM(
    SUM(EXTENDED_AMOUNT - DISCOUNT_AMOUNT)
) OVER (
    ORDER BY DATE_KEY
    ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
)

DAX

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
    )
)

Unbounded Window

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

MAX(cutoff) OVER ()

DAX

MAXX(ALL('table'), 'table'[cutoff])

SQL

SUM(amount) OVER ()

DAX

SUMX(ALL('table'), 'table'[amount])

CASE WHEN Translation

Conditional DISTINCTCOUNT

SQL

COUNT(DISTINCT CASE
    WHEN order_count > 1
    THEN customer_key
END)

DAX

CALCULATE(
    DISTINCTCOUNT('table'[customer_key]),
    'table'[order_count] > 1
)

Multi-Table Arithmetic

When expressions reference multiple tables:

• Preserve table qualification
• Use iterator functions
• 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: factsales (Many) → dimproduct (One)

SQL

SUM(dim_product.standard_cost * fact_sales.quantity_sold)

DAX

SUMX(
    'fact_sales',
    RELATED('dim_product'[standard_cost]) *
    'fact_sales'[quantity_sold]
)

Even when this measure is authored on the dimproduct table, the iterator table is still factsales because fact_sales is the "from" side of the relationship.

SQL

SUM(dim_product.standard_cost * fact_sales.quantity)

DAX

SUMX(
    'fact_sales',
    RELATED('dim_product'[standard_cost]) *
    'fact_sales'[quantity]
)

SQL

SUM(
    (EXTENDED_AMOUNT - DISCOUNT_AMOUNT)
    - (STANDARD_COST * QUANTITY)
)

DAX

SUMX(
    'fact_sales',
    ('fact_sales'[EXTENDED_AMOUNT] - 'fact_sales'[DISCOUNT_AMOUNT])
    -
    (
        RELATED('dim_product'[STANDARD_COST]) *
        'fact_sales'[QUANTITY]
    )
)

KPI Translation Patterns

Profit

SQL

SUM(revenue - cost)

DAX

SUMX(
    'fact',
    'fact'[revenue] - 'fact'[cost]
)

Margin %

SQL

SUM(profit)
/
NULLIF(SUM(revenue), 0)

DAX

DIVIDE(
    [Profit],
    [Revenue]
)

Return Rate %

SQL

SUM(return_amount)
/
NULLIF(SUM(original_sales_amount), 0)

DAX

DIVIDE(
    SUM('fact_returns'[return_amount]),
    SUM('fact_sales'[original_sales_amount])
)

Table Qualification Rules

Always Qualify Columns

Preferred:

'fact_sales'[sales_amount]

Avoid:

[sales_amount]

Relationship Translation

SQL Join Semantics

When SQL implies dimension lookup:

SQL

dim_product.standard_cost

inside fact aggregation becomes:

DAX

RELATED('dim_product'[standard_cost])

Translation Heuristics

Detect Iterator Requirement

Use X-iterators when:

• Expression contains operators inside aggregation
• More than one column appears inside SUM/AVG/etc.
• Arithmetic mixes dimensions and facts

Detect Measure References

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 (
    ORDER BY DATE_KEY
    ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
)

DAX

CALCULATE(
    SUM('fact_sales'[revenue]),
    DATESINPERIOD(
        'dim_date'[date_key],
        MAX('dim_date'[date_key]),
        -89,
        DAY
    )
)

Common SQL → DAX Mappings

Example Translations

Example 1

SQL

SUM(store_sales.ss_sales_price * store_sales.ss_quantity)

DAX

SUMX(
    'store_sales',
    'store_sales'[ss_sales_price] *
    'store_sales'[ss_quantity]
)

Example 2

SQL

SUM(ATTRIBUTED_REVENUE)
/
NULLIF(SUM(SPEND_AMOUNT), 0)

DAX

DIVIDE(
    SUM('fact_marketing'[ATTRIBUTED_REVENUE]),
    SUM('fact_marketing'[SPEND_AMOUNT])
)

Example 3

SQL

(
  COUNT(DISTINCT CASE
    WHEN CUSTOMER_ORDER_COUNT > 1
    THEN CUSTOMER_KEY
  END)
  /
  NULLIF(COUNT(DISTINCT CUSTOMER_KEY), 0)
) * 100

DAX

DIVIDE(
    CALCULATE(
        DISTINCTCOUNT('customer'[CUSTOMER_KEY]),
        'customer'[CUSTOMER_ORDER_COUNT] > 1
    ),
    DISTINCTCOUNT('customer'[CUSTOMER_KEY])
) * 100

Output Requirements

Generated DAX must:

• Be valid Power BI DAX syntax
• Use proper indentation
• Use uppercase DAX functions
• Prefer DIVIDE over /
• Prefer iterators over invalid scalar arithmetic
• Preserve business semantics exactly
• Avoid unnecessary CALCULATE wrappers
• Use RELATED for dimension attribute access
• Use measures when semantic reuse is implied

Important Semantic Differences

SQL is row-set based.

DAX is filter-context based.

Correct translation often requires:

• Iterator functions
• Context transition
• Relationship navigation
• Measure decomposition

Do not attempt direct token replacement.