# AR Gap Analysis Platform: Resume and Portfolio Guide

## User-confirmed context — 2026-10-18

> Source: direct user answers in the portfolio review. These are user-confirmed statements, not independently rerun repository or production measurements.

- **Ownership:** independently built the full report and semantic model.
- **Operational adoption:** more than 9 people (10+ management controllers and managers) use it at least weekly.
- **Current scale:** more than 70 million rows and more than 35 columns, as reported by the user. The 67,515,061-row figure below is an earlier recorded validation snapshot, not a measurement rerun on 2026-10-18.
- **Business purpose:** enables analysis that was impractical manually: monthly adjustment amounts, unexpected increases, billing-case investigation, historical monthly movements, and unusual plant-level spending. Charts and movement rules help identify the direction of changes and contributing cost types; users investigate anomalies rather than relying on a confirmed automatic anomaly detector.
- **Relationship to MGap:** a separate report supporting any selected month versus another month. MGap compares the current month with the prior month.
- **Unmeasured:** time savings, refresh/memory improvements and anomaly-detection rates.

> Existing draft variants and unanswered placeholders are retained. This confirmed context governs personal ownership, adoption and outcomes; unresolved draft claims are not newly verified.


> This guide is based on the source in this repository: TMDL, Power Query M, DAX, PBIR JSON, Node.js and PowerShell tooling, validation artifacts, and project briefs. Runtime figures come from saved validation outputs in [`_brief/spec-repair-status.md`](./_brief/spec-repair-status.md) and [`performance_recording/`](./performance_recording/). Placeholders in brackets, such as `[X hours]`, mark results that can't be measured from the code.

---

## 1. High-Level Project Overview

### One-line pitch

A Power BI analytics platform for finance teams. It compares any two monthly accounts-receivable (AR) snapshots, reconciles the net change case by case, and explains it as an auditable movement bridge. The validated dataset contains 67.5 million rows and about €1.25 billion of AR.

### Problem

- The finance and transport-billing teams receive monthly AR extracts as Parquet files, one folder per month. Each extract is a full snapshot of open billing cases.
- Subtracting this month's total from last month's gives a net gap. It doesn't explain the gap. A €9.85M net increase can hide €126M of gross increases offset by €117M of gross decreases.
- Analysts couldn't easily answer which cases are new, which disappeared, which grew or shrank, which changed status, or whether the movements reconcile to the cent.
- Snapshots also cover different processing-date ranges. Comparing them directly mixes real movement with differences in coverage.

### Solution

The project is a layered, declarative analytics system with source-controlled definitions:

1. **Ingestion layer (Power Query M).** Reads every Parquet file in a year folder and enforces a 35-column schema contract. It derives the snapshot identity from the folder name (`AR_2026_01`) and normalizes text, status, and currency types. See [`AR_2026.tmdl`](./All_Ar_Gap.SemanticModel/definition/tables/AR_2026.tmdl).
2. **Enrichment layer.** Joins a remote Excel cost-center reference, cleaned at the ingestion boundary, so movements can be grouped by legal entity, plant, domain, and scope. See [`Cost Centers.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Cost Centers.tmdl>).
3. **Semantic and analytics layer (DAX).** Contains 61 measures that:
   - validate the baseline/comparison pair;
   - restrict both snapshots to their shared reporting interval;
   - compare cases between snapshots and classify each case as new, disappeared, increased, decreased, status changed, or missing amount;
   - reconcile opening AR + movements = closing AR. See [`#Measures.tmdl`](./All_Ar_Gap.SemanticModel/definition/tables/%23Measures.tmdl).
4. **Presentation layer (PBIR).** A 9-page report with 242 visuals, synced slicers, a materiality filter, drill-through to case detail, and a waterfall bridge. See [`All_Ar_Gap.Report/definition/`](./All_Ar_Gap.Report/definition/).

---

## 2. Comprehensive Tech Stack & Tooling

> This isn't a web application. It has no `package.json`, `requirements.txt`, Dockerfile, or CI workflow. The stack below is what the repository actually uses.

### Languages & Runtimes

| Technology | Where / how it's used |
|---|---|
| **DAX** (Data Analysis Expressions) | 61 measures, 1,054 lines in `#Measures.tmdl`. Calculated tables: `DimDate`, 5 disconnected control tables, and `#Measures`. |
| **Power Query M** | Fact and lookup ingestion (`AR_2026`, `Cost Centers`), plus 4 shared Parquet transform expressions in [`expressions.tmdl`](./All_Ar_Gap.SemanticModel/definition/expressions.tmdl). |
| **TMDL** (Tabular Model Definition Language) | Text-based, Git-diffable semantic model. Compatibility level **1606**; semantic-model definition version **4.2**. |
| **PBIR / PBIP JSON** | Report definition version **4.0**, report schema **3.3.0**, visual container schemas **2.10.0 / 2.13.0**. |
| **JavaScript (Node.js)** | Scripts that generate, repair, and check PBIR/TMDL files (`tools/*.js`, `.tools/*.cjs`). |
| **PowerShell + ADOMD.NET** | [`query_local_model.ps1`](./tools/query_local_model.ps1) runs DAX against the local Analysis Services engine. |
| **Python** | [`redesign_nav.py`](./tools/redesign_nav.py) generates the navigation rail. |

### Frontend / UI (Report Layer)

- **Rendering engine:** Power BI Desktop with **PBIR** (the enhanced report format). Each page and each visual is a separate JSON file.
- **Layout:** 9 pages at 1920×1080, using `FitToPage` display.
- **Visual types (13):**
  - 84 slicers;
  - 15 new-style `cardVisual` KPI cards;
  - 10 bar charts and 1 clustered column chart;
  - 1 waterfall chart;
  - 2 line charts;
  - 6 matrices (`pivotTable`) and 2 tables (`tableEx`);
  - 2 action buttons;
  - 9 visual groups;
  - images, shapes, and textboxes.
- **Navigation / state:**
  - **10 slicer sync groups**, so filters persist across pages;
  - **64 page-navigation visuals** forming a persistent icon rail. It uses 8 custom SVG icons from `StaticResources/RegisteredResources`;
  - **2 drill-through pages** keyed on `Billing Cases_BillingCaseId`.
- **Styling:**
  - custom theme [`StellantisARGap-…json`](./All_Ar_Gap.Report/StaticResources/RegisteredResources/StellantisARGap-243882-42c7d508.json) with an 8-color palette, semantic `good`/`neutral`/`bad` colors, text classes, and visual styles;
  - layered on the Microsoft **Fluent 2** base theme (`Fluent2-CY26SU05`).
- **Measure-driven formatting:** the `AR Movement Color` measure returns hex colors for increase, decrease, and neutral. It's bound in 20 places.
- **Custom visual:** `ChicletSlicer1448559807354` is registered in `report.json`. No current visual uses it, so it's a candidate for cleanup.
- **Report settings:**
  - `exportDataMode: AllowSummarized`, which limits exports to summarized data;
  - enhanced tooltips;
  - stylable visual container headers.

### Backend / API (Semantic Model and Engine)

- **Engine:** in-memory Analysis Services / VertiPaq columnar store, using **Import** mode for every table.
- **Query protocol:** DAX over **XMLA / ADOMD.NET**. The tooling connects by discovering the `msmdsrv.port.txt` port of the local Desktop workspace.
- **Model structure:**
  - **Star-schema core:** the `AR_2026` fact table with a `DimDate` dimension and a `Cost Centers` dimension;
  - **5 disconnected parameter tables:** `Baseline Extraction`, `Comparison Extraction`, `Movement Class`, `Materiality Band`, and `Exception Class`. They hold user input and categories for display, with no filter relationships.
- **Data access options:**
  - `returnErrorValuesAsNull`;
  - `legacyRedirects`;
  - `sourceQueryCulture: fr-FR`, for French-locale source parsing;
  - auto date/time disabled (`__PBI_TimeIntelligenceEnabled = 0`).

### Database & Storage

| Layer | Detail |
|---|---|
| **Primary source** | Columnar **Apache Parquet** files read through `Folder.Files` and `Parquet.Document`, filtered by extension, `Hidden`, and `System` attributes. |
| **Reference source** | Excel workbook loaded over **HTTPS** from an internal document portal (`Web.Contents` + `Excel.Workbook`). |
| **Storage** | VertiPaq in-memory store. The model holds **9 tables, 82 columns, and 2 relationships**. |
| **Money type** | Fixed-decimal `Currency.Type` in M and `decimal` in TMDL, which avoids floating-point drift in financial sums. |
| **Keys** | `Table.AddKey` declares `Billing Cases_BillingCaseId` as a **non-primary** key, because uniqueness across extracts isn't verified. `DimDate[Date]` is marked `isKey`. |

### DevOps, Testing & Tooling

- **Source control:**
  - PBIP project format with text definitions, so Git diffs work;
  - Fabric `.platform` files keep stable `logicalId` identities;
  - `.gitignore` excludes dependency modules.
- **Authoring tools:** Power BI Desktop Developer Mode, TMDL View, and DAX Query View, according to the `PBI_ProTooling` annotation.
- **Code generation:** Node scripts generate visual IDs deterministically from SHA-1 hashes (`crypto.createHash('sha1')`), so reruns produce the same IDs.
  - Helpers include `makeCard`, `makeChart`, `makeSlicer`, and `mutateVisual`.
  - See [`implement_full_plan.js`](./tools/implement_full_plan.js) and [`repair_spec_report.js`](./tools/repair_spec_report.js).
- **Model patching:** [`repair_spec_model.js`](./tools/repair_spec_model.js) inserts or replaces measures and keeps existing metadata such as format strings and display folders. Calling it again updates the same measures instead of duplicating them.
- **Validation harness:**
  - [`build_repair_checks.js`](./tools/build_repair_checks.js) reads every measure from TMDL and writes **11 DAX query scripts** to `.artifacts/spec-repair/`. Each script carries query-scoped `DEFINE MEASURE` copies of the measures.
  - The scripts cover valid, equal, and reversed extraction pairs, history behavior, and materiality.
  - `query_local_model.ps1` runs them through ADOMD.NET with a 180-second timeout and writes JSON results.
- **Binding checks:** the binding check covered **246 JSON files and 278 entity/property references**, with no unresolved bindings.
- **Performance profiling:** Power BI Performance Analyzer capture in [`PowerBIPerformanceData.json`](./performance_recording/PowerBIPerformanceData.json), with 241 events.
- **Agent tooling:** `apm.yml` pulls `microsoft/skills-for-fabric` skills (`powerbi-report-cli`, `semantic-model-authoring`, `sqldw-cli`).
- **Documentation:**
  - [`MEASURES_GUIDE.md`](./MEASURES_GUIDE.md) documents all 61 measures by functional meaning, technical rule, and worked example;
  - 58 of the 61 measures have inline `///` descriptions;
  - the `_brief/` folder holds the report specification, the optimization audit, and the repair status.

---

## 3. Architecture & Technical Highlights

### Highlight 1: Two-snapshot comparison engine with disconnected parameter tables

**Files:**
- [`Baseline Extraction.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Baseline Extraction.tmdl>)
- [`Comparison Extraction.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Comparison Extraction.tmdl>)
- `Comparison Selection Count` and `Comparison Selection Status` in `#Measures.tmdl`

**Before:** `Sum_Previous_Month` / `Sum_New_Month` took `MIN` and `MAX` of the extraction names in the current filter context. This version is preserved in [`TMDLScripts/Script 1.tmdl`](<./All_Ar_Gap.SemanticModel/TMDLScripts/Script 1.tmdl>). Any business filter that removed one snapshot's rows could silently change which snapshots were compared.

**After:**

- Two calculated tables, built with `SELECTCOLUMNS(FILTER(ALL(...)))`, list the extraction names. They have **no relationships**, so business slicers can't change the selected pair.
- A validation measure returns `2` only when both sides are selected and `baseline < comparison`. It works as a guard: all 14 movement measures return blank unless it passes.
- `Comparison Selection Status` shows user-facing validation messages for each failure: missing selection, same snapshot twice, or wrong order.
- **Design pattern:** a parameter-table / "what-if" pattern combined with a single validation measure that every dependent measure checks.

### Highlight 2: Case-level movement classification with SUMMARIZECOLUMNS and TREATAS

**File:** `#Measures.tmdl`: `New Case Amount`, `Disappeared Case Amount`, `Existing Case Increase/Decrease`, `Gross AR Increase/Decrease`, `Material Exception Count`, `Status Changed Case Count`.

**How it works:**

- Each measure builds an in-memory `_Pair` table: `SUMMARIZECOLUMNS` grouped by billing-case ID. For each case it computes `@RowsA`, `@RowsB`, `@AmountA`, `@AmountB`, and in some measures `@StatusA` and `@StatusB`.
- `REMOVEFILTERS` + `TREATAS({_A, _B}, ...)` replace the snapshot filter with exactly the two selected snapshots.
- A case's class depends on whether it **has rows** in each snapshot, not on whether its amount is zero:

  | Rows in baseline | Rows in comparison | Class |
  |---|---|---|
  | 0 | > 0 | New |
  | > 0 | 0 | Disappeared |
  | > 0 | > 0 | Increased, Decreased, Status Changed, or Unchanged |

  As a result, a real €0 balance isn't confused with a missing case.
- When rows exist but the amount is blank, the case is marked `Missing amount` and **excluded** from the amount movements. Blank amounts are never treated as zero.
- **Scale:** the repository has **14 `_Pair` builders, 18 `TREATAS` calls, and 54 `KEEPFILTERS` predicates**.

### Highlight 3: Reconciliation to the cent with a waterfall bridge

**Files:**
- `#Measures.tmdl`: `Reconciliation Check`, `Reconciliation Status`, `Movement Amount`
- [`Movement Class.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Movement Class.tmdl>)

**How it works:**

- Six ordered `DATATABLE` rows (Opening AR → New → Increases → Decreases → Disappeared → Closing AR) feed a `SWITCH` measure. The waterfall chart uses them as its categories.
- `Reconciliation Check` = `ROUND(Opening + New + Increases + Decreases + Disappeared − Closing, 2)`.
- `Reconciliation Status` reports **"Reconciled to cents"** when `|residual| < 0.005`. It reports "Cannot reconcile – missing amounts" when amounts are missing.
- **Measured result:** on January → February 2026 (≈€1.247B → €1.257B), the unrounded residual was **€0.00002551**.

### Highlight 4: Shared reporting interval for snapshots with different coverage

**File:** `#Measures.tmdl`: `Shared Interval Start/End`, `Shared Baseline/Comparison AR`, `Shared Interval Gap`, `Shared Interval Label`.

- The interval starts at `DATE(YEAR, MONTH, 1)` of the later first processing date and ends at `EOMONTH` of the earlier last processing date. Both are computed with `REMOVEFILTERS('AR_2026')` and `REMOVEFILTERS('DimDate')`, so slicers can't move the boundaries.
- Every movement measure applies the interval with `KEEPFILTERS` date predicates. These intersect with the user's own date filters instead of replacing them.
- **Documented trade-off:** month boundaries are inferred from the earliest and latest transaction dates. That doesn't prove a month is complete. [`model-optimization-audit.md`](./_brief/model-optimization-audit.md) flags this and recommends a coverage-metadata table.

### Highlight 5: Ingestion with a schema contract and error handling at the boundary

**Files:** [`AR_2026.tmdl`](./All_Ar_Gap.SemanticModel/definition/tables/AR_2026.tmdl) (12 named M steps) and [`Cost Centers.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Cost Centers.tmdl>).

**AR_2026 query:**
- A single `DataColumns` list defines the expected schema. Missing required columns **raise an error**; extra source columns are ignored.
- Each file is projected to that schema **before** expansion (`Table.SelectColumns(#"Transform File"([Content]), DataColumns)`). Unused columns are never loaded, and the Sample-File schema can't drift.
- Binary content and folder paths are removed early. The snapshot ID is computed **once per file**, before rows are expanded.
- One `Table.TransformColumns` pass handles all text columns with a null-safe `CleanText` function. Status is lowercased for consistent comparison between snapshots.

**Cost Centers query:**
- The workbook is loaded over HTTPS.
- `Table.ReplaceErrorValues` runs **before** any transformation, so Excel errors can't spread to later steps.
- Text is cleaned and trimmed, then rows are dropped when the cost center is null, empty, `N/A`, or `NA` in any case.

### Highlight 6: Data-quality checks and materiality filtering

**Files:**
- `#Measures.tmdl`: display folders `Data Quality` (6 measures) and `Materiality` (2 measures)
- [`Materiality Band.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Materiality Band.tmdl>)
- [`Exception Class.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Exception Class.tmdl>)

**Data-quality measures:**
- `Missing Key Rows` and `Missing Amount Rows`;
- `Repeated Case Extraction Rows`, computed as `COUNTROWS − COUNTROWS(SUMMARIZE(...))`;
- `Missing Month Extraction Cells`, which counts empty cells in a `CROSSJOIN` of reporting months by snapshots;
- `Extraction Row Count Change %` versus the previous snapshot.

**Materiality filtering:**
- Bands are **€0, €10K, €100K, and €1M**.
- `Exception Visible` and `Material Movement Visible` return 1 or 0 and are used as **visual-level filters**. This keeps only rows above the threshold without adding relationships.
- At the €0 threshold, `Status Changed` and `Missing amount` cases stay visible because they matter even without an amount change.

---

## 4. Quantifiable Metrics & Scope (From the Codebase)

> **Equivalent of API endpoints and routes:** the project has no HTTP API. The closest equivalents are the **61 DAX measures** and **9 report pages**, which visuals query through the Analysis Services engine.

### Semantic model

| Metric | Value |
|---|---|
| Tables | **9**: 2 imported with M, 7 DAX calculated tables, including 5 disconnected parameter tables |
| Columns | **82** in total: 37 fact, 13 cost-center, 24 date |
| Relationships | **2**: `AR_2026 → DimDate` (many-to-one, bidirectional cross-filter) and `AR_2026 → Cost Centers` (many-to-one, single direction) |
| DAX measures | **61**, organized in 5 display folders: Comparison Context 15, Comparison Controls 16, Movement Reconciliation 15, Data Quality 6, Materiality 2. Seven measures sit outside any folder. |
| Measure documentation | **58 of 61** have `///` descriptions; all 61 are documented in `MEASURES_GUIDE.md` |
| Measure source size | **1,054 lines** of TMDL/DAX |
| Case-pair materializations | **14** `SUMMARIZECOLUMNS` `_Pair` builders, **18** `TREATAS`, **54** `KEEPFILTERS` |
| M shared expressions | **4**: parameter, sample file, sample transform, `Transform File` function |
| Fact ingestion | **35-column** schema contract + 2 derived columns; **12** named transform steps |
| Date dimension | **24** attributes, including ISO week/year, week start/end, and current-period flags |
| Linguistic metadata | **20,221-line** `en-US` culture file for Q&A synonyms |

### Report and UI

| Metric | Value |
|---|---|
| Pages | **9**: 7 navigable analysis pages + 2 drill-through pages |
| Visuals | **242**, across **13** visual types |
| Slicers / sync groups | **84** slicers in **10** sync groups |
| Navigation visuals | **64** page-navigation actions; **8** custom SVG navigation icons |
| Filters | **41** visual-level + **7** page-level filters |
| Measure-driven conditional formatting | `AR Movement Color` bound in **20** places |
| Distinct model fields bound by visuals | **64** |

### Data scale and correctness (runtime evidence from `_brief/spec-repair-status.md`)

| Metric | Value |
|---|---|
| Fact rows loaded | **67,515,061** |
| AR under analysis, Jan → Feb 2026 | **€1,246,813,248 → €1,256,666,770**, net **+€9,853,522** |
| Gross movement explained | **+€126.5M** increases / **−€116.7M** decreases |
| Reconciliation residual | **€0.0000255**; tolerance `< 0.005`; result rounded to 2 decimals |
| Cases classified in one comparison | **151,831** increased, **147,020** decreased, **169,592** status changes |
| Material exceptions found (default threshold) | **1,121,667** |
| Repeated case/snapshot rows flagged for grain review | **40,899,667** |

### Performance-related constants and baselines

| Item | Value |
|---|---|
| Materiality thresholds | €0 / €10,000 / €100,000 / €1,000,000 |
| Reconciliation tolerance | €0.005 (half a cent) |
| ADOMD query timeout | 180 s (`CommandTimeout = 180`) |
| Performance Analyzer baseline | 241 events and 5 DAX queries. Slowest visual query **77.2 s**; mean query event **≈7.2 s**. This is a baseline for `[target]` optimization work. |
| Automated tests | No unit-test framework. Validation uses **11 DAX validation queries** (5 pair scenarios, history, materiality, smoke, verify, optimized) plus a binding check of **246 files / 278 references** |

---

## 5. Ready-to-Use Resume Bullet Points (XYZ Format)

### Variation 1: Full-Stack / General Software Engineering focus

1. **Independently built** a Power BI AR gap-analysis platform (TMDL + PBIR + DAX + M), used **at least weekly by 10+ management controllers and managers**. It supports any selected month versus another month over a user-confirmed current volume of **70M+ rows and 35+ columns**, enabling analysis that was impractical manually. The recorded **67.5M-row / €1.25B** validation snapshot below remains historical evidence; no manual time-saving baseline is claimed.
2. **Engineered** a case-level comparison engine (61 DAX measures, 14 in-memory case-pair tables using `SUMMARIZECOLUMNS` + `TREATAS`). It splits a single net gap into **six classes of movement**, explaining **€243M of gross movement** behind a €9.9M net change.
3. **Automated** report generation with Node.js scripts that write PBIR visual JSON using deterministic SHA-1 IDs. A complete **9-page, 242-visual** report was rebuilt in `[Y minutes]` instead of `[Z hours]` of manual Desktop editing, and reruns produce the same IDs.
4. **Built** a validation harness: 11 generated DAX scenario queries run through ADOMD.NET against the local engine, plus a binding check of **278 model references across 246 files**. Releases had **zero unresolved bindings**.

### Variation 2: Backend / Systems Architecture focus

5. **Designed** a star-schema semantic model (fact + 2 dimensions + 5 disconnected parameter tables) that **isolates baseline/comparison snapshot selection from business filters**. One validation guard read by every dependent measure removed a class of silent wrong-pair comparisons.
6. **Optimized** Parquet ingestion by projecting each file to a 35-column schema contract **before expansion**. The query also drops binary data early and computes snapshot identity once per file, cutting refresh time by `[X%]` and model memory by `[Y MB]`.
7. **Hardened** data integrity with fixed-decimal currency types, presence-based case classification, and missing-amount exclusion. Six data-quality measures surfaced **40.9M repeated case/snapshot rows** for source-grain review instead of letting them distort totals silently.
8. **Profiled** report queries with Performance Analyzer (77.2 s slowest baseline) and a static model audit. The audit found 14 repeated case-pair calculations as the main query cost and led to a snapshot-summary aggregation design targeting `[X%]` faster visuals.

### Variation 3: Frontend / Product Engineering focus

9. **Delivered** a 9-page finance report with persistent navigation, synced slicers and billing-case drill-through. Controllers and managers use charts and movement rules to quantify monthly adjustments, inspect historical movements, trace increases to billing cases and cost types, and investigate plant-level spending anomalies. Investigation time remains `[X min]` to `[Y min]` (unmeasured).
10. **Designed** materiality-driven exception views (€10K / €100K / €1M bands) using measure-based visual filters and colors set by DAX (`AR Movement Color` in 20 places). **1.12M raw exceptions** were narrowed to the `[N]` cases finance needed to review; the report is used at least weekly by **10+ management controllers and managers**.

---

## 6. Interview Prep: Deep-Dive Talking Points

### Talking Point A: Making snapshot comparison reliable regardless of filters

**Files:**
- [`TMDLScripts/Script 1.tmdl`](<./All_Ar_Gap.SemanticModel/TMDLScripts/Script 1.tmdl>) (legacy version)
- [`Baseline Extraction.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Baseline Extraction.tmdl>)
- [`Comparison Extraction.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Comparison Extraction.tmdl>)
- `Comparison Selection Count` / `Total_Gap` in [`#Measures.tmdl`](./All_Ar_Gap.SemanticModel/definition/tables/%23Measures.tmdl)

- **Situation:** The first version computed the gap as `MAX(extraction) − MIN(extraction)` over the extractions in the current filter context. If a user filtered by plant or G/L account and one snapshot had no rows in that slice, the comparison silently switched snapshots or returned a misleading gap.
- **Task:** Make the selected snapshot pair independent of business filters, validate the user's selection, and keep all dependent measures consistent.
- **Action:**
  - Replaced the filter-dependent approach with two **disconnected** calculated tables.
  - Added a single validation measure (`= 2` only if both sides are selected and `baseline < comparison`) that all 14 movement measures check before computing.
  - Applied the pair with `REMOVEFILTERS` + `TREATAS`.
  - Showed a clear corrective message for each invalid state.
  - Kept the old `MIN`/`MAX` measures out of the canonical model, preserving them only in an un-run TMDL script.
  - Validated with generated DAX scenarios: valid, equal (`02/02`), reversed (`03/01`), and wide (`01/08`) pairs.
- **Result:** Business slicers can't change the selected pair, and invalid selections produce explicit messages instead of wrong numbers. Validation produced a reconciled Jan → Feb bridge on **€1.25B**.
- **Trade-off to discuss:** Disconnected tables need `TREATAS` in every measure. That adds DAX complexity and query cost, accepted in exchange for correctness. The pair uses lexical order (`_A < _B`), which works because names follow the zero-padded `AR_YYYY_MM` pattern.

### Talking Point B: Explaining the net gap with a case-level bridge that reconciles to the cent

**Files:**
- `New/Disappeared Case Amount`, `Existing Case Increase/Decrease`, `Gross AR Increase/Decrease`, `Reconciliation Check`, `Reconciliation Status`, `Movement Amount` in `#Measures.tmdl`
- [`Movement Class.tmdl`](<./All_Ar_Gap.SemanticModel/definition/tables/Movement Class.tmdl>)

- **Situation:** Finance saw a +€9.85M net change but couldn't tell how much came from new billing cases, closed cases, or revaluations. Treating a missing amount as zero would have made the bridge look balanced while hiding data problems.
- **Task:** Split the net change into additive categories that provably sum from opening AR to closing AR, without hiding data defects.
- **Action:**
  - Built a per-case `_Pair` table with `SUMMARIZECOLUMNS` (row counts and summed amounts for each snapshot).
  - Classified each case by **whether it has rows** in each snapshot, not by whether its amount is zero.
  - Excluded present-but-blank amounts from the bridge and labeled them `Missing amount`.
  - Built a 6-row ordered `DATATABLE` that feeds a waterfall chart through a `SWITCH` measure.
  - Added a `ROUND(..., 2)` reconciliation residual with a `< 0.005` "Reconciled to cents" status, and blocked reconciliation when amounts are missing.
- **Result:** Jan → Feb was split into **+€124.2M new, −€105.2M disappeared, +€2.3M increases, −€11.4M decreases**. The unrounded residual was **€0.0000255**. Classification covered **151,831 increased and 147,020 decreased cases**.
- **Trade-off to discuss:** The `_Pair` logic is repeated in 14 measures, which is the main suspected query cost. Mention the planned snapshot-summary aggregation table, and why you wouldn't aggregate blindly: it would drop the slicer columns that visuals filter on. Also note that classification within a filtered slice can count a case moving between G/L accounts as "disappeared" in one account and "new" in the other. That's an explicit policy decision.

### Talking Point C: Scaling ingestion and checking results for a 67.5M-row model with no CI

**Files:**
- [`AR_2026.tmdl`](./All_Ar_Gap.SemanticModel/definition/tables/AR_2026.tmdl) (M partition)
- [`expressions.tmdl`](./All_Ar_Gap.SemanticModel/definition/expressions.tmdl)
- [`build_repair_checks.js`](./tools/build_repair_checks.js)
- [`query_local_model.ps1`](./tools/query_local_model.ps1)
- [`spec-repair-status.md`](./_brief/spec-repair-status.md)

- **Situation:** Monthly Parquet extracts arrive in nested folders. The model reached **67.5M rows**. The project had no CI, no test framework, and no server. The only engine was the local Power BI Desktop Analysis Services instance.
- **Task:** Make ingestion fail early when the schema drifts and keep memory use low. Also give the DAX logic repeatable checks against real data.
- **Action:**
  - **Ingestion:**
    - Filtered to `.parquet` files that aren't hidden or system files.
    - Enforced a 35-column contract with `Table.SelectColumns`, which errors on missing columns, applied to each file before expansion.
    - Computed the snapshot ID once per file from the folder path.
    - Ran one null-safe text-clean pass and declared a non-primary key, because uniqueness across snapshots isn't proven.
  - **Testing:**
    - Wrote a Node script that reads all measures from TMDL with a regex and builds query-scoped `DEFINE MEASURE` blocks. It generates DAX scenario files: valid, equal, and reversed pairs, history, and materiality.
    - Wrote a PowerShell runner that finds the Desktop engine's dynamic port and runs each script through ADOMD.NET with a 180-second timeout, writing JSON results.
- **Result:**
  - Measure logic can be tested before saving it into the canonical model.
  - Results are reproducible and compared as files.
  - The binding check covered **278 references across 246 files** with no unresolved bindings.
  - The checks surfaced **40.9M repeated case/snapshot rows** and a one-cent rounding disagreement between expected and computed history values. Both were documented instead of being hidden by changing the arithmetic.
- **Trade-off to discuss:**
  - Checks against a live local engine replace a real test suite. They're realistic but not hermetic, and a combined three-pair query **timed out at 180 s** on the full population.
  - **Next step:** run the checks against a representative sample model in CI, or use Fabric/XMLA endpoints.

---

### Quick-reference: honest scope statements for interviews

- **Security:** Report exports are limited to summarized data (`AllowSummarized`). There is **no row-level security, authentication layer, or RBAC** defined in the model. Don't claim otherwise.
- **Testing:** Validation uses **scripted scenario DAX queries plus structural binding checks**. There is no automated unit-test framework or CI pipeline.
- **Real-time:** Import mode with refresh. There are no streaming, DirectQuery, or webhook integrations.
- **Ownership framing:** The repository includes AI-agent tooling (`apm.yml`, Fabric skills). If asked, describe your role as owning the design decisions, data rules, and validation, and using agents to speed up PBIR/TMDL edits.