dax-sql

A DAX to SQL compiler for Power BI / Tabular semantic models: a DAX query in, one SQL SELECT out, with DAX's semantics. Tested on DuckDB against Power BI's own answers. No dependencies.

Initially written by N.S. Devaraj.

npm install dax-sql

import { createCompiler } from 'dax-sql';
import { tmslToModel } from 'dax-sql/formats/tmsl.js';   // or formats/ossie.js for Apache Ossie

const model = tmslToModel(JSON.parse(bimText));           // a model.bim
const dax = createCompiler(model, { tableSource: t => `read_parquet('data/${t.name}.parquet')` });

const { sql, columns } = dax.compile(`
  EVALUATE
  SUMMARIZECOLUMNS('Date'[Year], "Sales", [Sales Amount])
  ORDER BY 'Date'[Year]`);
// sql: one SELECT for the engine; columns: [{ name: 'Year', type: 'int', ... }, { name: 'Sales', ... }]

• In: a semantic model as dax-sql's model object (DESIGN.md; formats/ reads one from a TMSL model.bim or an Apache Ossie model whose metrics are DAX or SQL), a DAX query or a SQL SELECT over the model, options (DESIGN.md); or any SQL with SELECTs over the model as relations in it.
• Out: one SQL SELECT and its columns; for a statement, its SQL with each of them compiled in place.
• Errors: SYNTAX, SEMANTIC, UNSUPPORTED.
• Tested: against Power BI's own answers, in conformance/.

Not implemented

• Object-level security, dynamic format strings, the window functions' reset argument, and anything not listed in DESIGN.md.
• FORMAT is en-US only, with a constant pattern; model format strings are not applied.
• DATEDIFF with WEEK counts Sunday week boundaries.

Departures from DAX, by design

• Text is case-sensitive in grouping, DISTINCT, sorting (by code point, not culture) and relationships; comparisons, IN and table filters are not, as in DAX.
• Text keeps its trailing spaces (Power BI drops them on load): "a " and "a" are two values, in grouping, filters, relationships and LEN. Trim them in the data if they matter.
• A column that mixes numbers and text comes back as text.
• IFERROR returns its first argument: SQL has no error values.
• A query that reads one table only (VALUES(Customer[City]), a slicer's list) has no blank row, though fact rows have keys the table lacks: it is a plain SELECT DISTINCT.