[PR] feat(microsoft): parse DAX in full, classify every DAX function, translate composition [ossie-converters]

["MrBlack1995 (via GitHub)" <gi...@apache.org>]

MrBlack1995 opened a new pull request, #27:
URL: https://github.com/apache/ossie-converters/pull/27

   ## Summary
   
   Second slice of apache/ossie#584, following #6. Three changes to the opt-in `--translate-dax` import path; the default output is still unchanged.
   
   1. **A full DAX parser** (`_dax_parser.py`, no dependencies) replaces the single-call regex. It parses the whole expression grammar with DAX precedence (including `^` binding tighter than sign, so `-2^2` is `-(2^2)`), function calls, `VAR` / `RETURN`, column and measure references, table constructors and comments. The point is not to translate more of it, but that anything outside the translated set is now recognised and refused **by name** instead of failing a pattern match.
   2. **A registry of every DAX function** (`_dax_catalog.py`, 392 entries: tier and verdict). Every refusal now says why, so a reader can tell a later tier of this converter from a spec gap or a construct with no portable form:
      ```
      COUNTROWS has an exact OSSIE_SQL_2026 form that is not translated yet
      CALCULATE needs filter-context semantics, which the Apache Ossie specification does not define yet
      TOTALYTD needs window measures (time intelligence), which the Apache Ossie specification does not define yet
      SUMMARIZE returns a table; calculated tables are not metric expressions
      ```
      This also answers question 3 on apache/ossie#584 for now: the catalog is a Python module, like the existing converters. It is plain data, so moving it to a language-neutral file later is mechanical.
   3. **Composition** over the six aggregates from #6, each rule chosen to be exact under DAX's documented BLANK semantics.
   
   ## What changes compared with #6
   
   The review on #6 noted that compound expressions and trailing comments are not translated by the regex. This PR changes those two on purpose, rule by rule. Everything else stays refused.
   
   | DAX | OSSIE_SQL_2026 | Why it is exact |
   |---|---|---|
   | `DIVIDE([Total Sales], [Customers])` | `SUM(Sales.Amount) / NULLIF(COUNT(Sales.Customer), 0)` | DAX returns BLANK on a zero or BLANK denominator; `NULLIF` gives NULL in both cases. This is also the form `_sql_to_dax` turns back into `DIVIDE`, so the pair round-trips. |
   | `[Gross] - [Total Sales]` | `CASE WHEN SUM(Sales.AmountWithTax) IS NULL AND SUM(Sales.Amount) IS NULL THEN NULL ELSE COALESCE(SUM(Sales.AmountWithTax), 0) - COALESCE(SUM(Sales.Amount), 0) END` | DAX treats BLANK as 0 in `+` and `-` unless both sides are BLANK (`BLANK + 5 = 5`, `BLANK + BLANK = BLANK`), where SQL `NULL + 5` is NULL. |
   | `[Total Sales] + 1` | `COALESCE(SUM(Sales.Amount), 0) + 1` | Same rule; the literal is never BLANK, so no guard is needed. |
   | `[Total Sales] * 1.1` | `SUM(Sales.Amount) * 1.1` | BLANK times anything is BLANK, as NULL is in SQL. |
   | `[Total Sales] / 1000` | `SUM(Sales.Amount) / 1000` | Only by a non-zero literal. |
   | `VAR s = [Total Sales] VAR c = [Customers] RETURN DIVIDE(s, c)` | `SUM(Sales.Amount) / NULLIF(COUNT(Sales.Customer), 0)` | Variables are substituted; at the top level of a measure a variable and its use share one context. |
   | `SUM(Sales[Amount]) // trailing comment` | `SUM(Sales.Amount)` | Comments are parsed, not pattern-matched. |
   
   Measure references (`[Total Sales]`) are expanded inline. At the top level of a measure there is no row context, so a referenced measure evaluates in the same filter context as the measure that references it. A measure that references itself, an unknown measure, or a referenced measure that is itself refused is refused, and the reason names the referenced measure.
   
   Still refused, each with a reason:
   
   | DAX | Why |
   |---|---|
   | `DIVIDE(a, b, 0)` | The documentation does not say whether the alternate result applies to a BLANK denominator as well as zero, so it is not guessed. |
   | `[Total Sales] / [Customers]` | The `/` operator returns Infinity or NaN on a zero or BLANK divisor, which has no portable form; `DIVIDE` is translated instead. |
   | `SUM(ROUND(Sales[Amount], 2))`, `SUM([Amount])` | Nested calls inside an aggregate and unqualified columns, as in #6. |
   | `^`, `&`, comparisons, logical operators, `-` applied to an expression | Later tiers. |
   | `COUNT`, `DISTINCTCOUNT` | Unchanged from #6 (TRUE/FALSE values, BLANK counted as distinct). |
   
   One reading is shared with `_sql_to_dax` rather than settled by the specification: `OSSIE_SQL_2026` division is taken to be true division, as DAX division is. The specification lists `/` without saying whether integer division truncates, so this is worth stating here.
   
   ## Validation
   
   - `microsoft`: **933 passed, 2 skipped**; coverage 96.10% (the three new modules at 100%); `ruff check` clean.
   - New tests:
     - parser: precedence and associativity, escapes, comments, `VAR`, table constructors, malformed input;
     - every one of the 392 registry entries run through the real translator: each either translates or is refused with a reason that names it;
     - golden tests for each composition rule and each refusal;
     - the emitted SQL executed in SQLite and compared with DAX's documented BLANK results (`BLANK + BLANK`, `BLANK + 5`, `BLANK * 5`, `DIVIDE` by BLANK and by zero);
     - an integration test on `sales_model.bim` with measures that reference each other.
   - On `sales_model.bim` with composed measures added: flag off output unchanged; flag on output passes `ossie/validation/validate.py`; exporting the flag on document back to Power BI gives the original DAX for every measure.
   
   ## Related Issues
   
   Refs apache/ossie#584. Follows #6.
   
   ## Checklist
   
   ### Documentation
   - [x] The converter's `README.md` is updated: `--translate-dax` in the flag table, and a short "DAX measures (opt-in)" section listing what is translated
   
   ### Tests
   - [x] All existing tests pass
   - [x] New functionality is covered by tests
   
   ### Compliance
   - [x] ASF license headers are present on all new source files
   - [x] No third-party dependencies are added (the parser is standard library only)
   
   This pull request and its description were written by Isaac.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@ossie.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org