sql-to-dax

# 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: ```sql column_name ``` ```sql table.column_name ``` ```sql 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 ```sql dim_product.`standard cost` ``` ### DAX ```dax 'dim_product'[standard cost] ``` --- ## Unqualified Column Resolution If a column is referenced without a table qualifier: ```sql 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 ```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: > > ```dax > SUM('fact_returns'[ORIGINAL_SALES_AMOUNT]) > ``` > > It must **not** fall through to the iterator form: > > ```dax > SUMX('fact_returns', ORIGINAL_SALES_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 ```sql SUM(fact_sales.`sales amount` - DISCOUNT_AMOUNT) ``` ### DAX ```dax SUMX( 'fact_sales', 'fact_sales'[sales amount] - 'fact_sales'[DISCOUNT_AMOUNT] ) ``` --- # Core Translation Rules --- ## 1. Aggregate Functions ### SQL ```sql SUM(column) ``` ### DAX ```dax SUM('table'[column]) ``` --- ### SQL ```sql AVG(column) ``` ### DAX ```dax AVERAGE('table'[column]) ``` --- ### SQL ```sql COUNT(DISTINCT column) ``` ### DAX ```dax DISTINCTCOUNT('table'[column]) ``` --- ## 2. Arithmetic Inside Aggregations If arithmetic occurs inside SUM/AVG/etc., use iterator functions. ### SQL ```sql SUM(price * quantity) ``` ### DAX ```dax SUMX( 'table', 'table'[price] * 'table'[quantity] ) ``` --- ### SQL ```sql SUM(revenue - discount) ``` ### DAX ```dax SUMX( 'table', 'table'[revenue] - 'table'[discount] ) ``` --- ### SQL ```sql AVG(quantity * cost) ``` ### DAX ```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 | 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 ```sql SUM(sales) / NULLIF(SUM(cost), 0) ``` ### DAX ```dax DIVIDE( SUM('table'[sales]), SUM('table'[cost]) ) ``` --- ## Nested NULLIF ### SQL ```sql 365 / NULLIF(metric, 0) ``` ### DAX ```dax DIVIDE( 365, [metric] ) ``` --- # Percentage Calculations --- ### SQL ```sql (metric / total) * 100 ``` ### DAX ```dax DIVIDE( [metric], [total] ) * 100 ``` --- # ROUND Translation --- ### SQL ```sql ROUND(expression, 2) ``` ### DAX ```dax ROUND(expression, 2) ``` --- # DIV0 Translation DIV0 means divide-by-zero-safe division. ### SQL ```sql DIV0(a, b) ``` ### DAX ```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 ```sql SUM(metric) OVER ( ORDER BY DATE_KEY ROWS BETWEEN 89 PRECEDING AND CURRENT ROW ) ``` ### DAX ```dax CALCULATE( [metric], DATESINPERIOD( 'dim_date'[date_key], MAX('dim_date'[date_key]), -89, DAY ) ) ``` --- ## Rolling Window over a Nested Aggregate ### SQL ```sql SUM( SUM(EXTENDED_AMOUNT - DISCOUNT_AMOUNT) ) OVER ( ORDER BY DATE_KEY ROWS BETWEEN 89 PRECEDING AND CURRENT ROW ) ``` ### DAX ```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: ```dax 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 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'))` | ### SQL ```sql MAX(cutoff) OVER () ``` ### DAX ```dax MAXX(ALL('table'), 'table'[cutoff]) ``` ### SQL ```sql SUM(amount) OVER () ``` ### DAX ```dax SUMX(ALL('table'), 'table'[amount]) ``` --- # CASE WHEN Translation --- ## Conditional DISTINCTCOUNT ### SQL ```sql COUNT(DISTINCT CASE WHEN order_count > 1 THEN customer_key END) ``` ### DAX ```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: `fact_sales (Many) → dim_product (One)` ### SQL ```sql SUM(dim_product.standard_cost * fact_sales.quantity_sold) ``` ### DAX ```dax SUMX( 'fact_sales',