# Functional Testing

> Use when defining or executing automated tests for DAX measures against expected values. Triggers: "run tests", "test measures", "functional test", "validate DAX output", "test definition", "expected vs actual", "cross-validate CSV", "run_tests.py", "test execution report", "measure accuracy check".

- Skill: `natalinio/functional-testing` (Agent Skill, multi-file: 4 files)
- Install (CLI): `npx skillmds@latest add natalinio/functional-testing`
- Raw SKILL.md: https://api.skillmd.com/api/skills/natalinio/functional-testing/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: natalinio (https://skillmd.com/u/natalinio)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/natalinio/functional-testing

---


# Skill: Functional Testing & Calculation Validation

## Purpose
Validate DAX measure correctness, relationship behavior, fiscal-calendar behavior, edge cases, and performance before production deployment. Cover: unit tests, integration tests, scenario tests, edge cases, and performance benchmarks.

## Input / Output

| | |
|---|---|
| **Input** | All TMDL files (model object registry), `<ProjectName>/tests/quality_review.md` |
| **Output** | `<ProjectName>/tests/tests_definition.json`, `<ProjectName>/tests/tests_execution.md` |

## Prerequisites — MANDATORY

1. ✅ **Model loads successfully** in Power BI Desktop without errors or warnings
2. ✅ **Mock data imported** — All CSV files successfully loaded into tables
3. ✅ **Fiscal test mode identified** — either:
  - the model exposes a `Parameters` table with `ParameterName` and `ParameterValue` columns for dynamic fiscal-start testing, or
  - the model encodes a fixed fiscal calendar directly in DAX and the test plan uses representative boundary dates instead of parameter mutation
4. ✅ **Date table marked** — the dedicated date dimension is marked as Date Table only after data load, using the model's primary date column
5. ✅ **PBIP path budget validated** — PBIP item names and folder path are short enough that Desktop-generated metadata with autogenerated names stays below the Power BI Desktop save limit
6. ✅ **Relationships active** — All relationships visible in Model View
7. ✅ **Analysis Services connection preflight completed** — the local Desktop workspace port is either auto-detected from `AnalysisServicesWorkspaces/**/msmdsrv.port.txt` or explicitly supplied, and a smoke-test query succeeds before the full run
8. 📋 **Test plan documented** — Specification available for expected results comparison

If RLS is implemented, also reference `.github/references/security-rls-best-practices.md`. For RLS test scenarios (View-as checks, deny-by-default, bidirectional propagation), see `.github/skills/functional-testing/references/functional-testing-methodology.md`.

---

## Testing Mode

**Mode A (Manual)**: User executes tests in Power BI Desktop. Follow Phase 1–6 test suites in `.github/skills/functional-testing/references/functional-testing-methodology.md`.

**Mode B (Agent-Assisted — Recommended)**: Agent builds model registry → generates `tests_definition.json` → executes shared runner → produces `tests_execution.md`. Follow Steps B.0–B.5 below.

---

## Mode B: Agent-Assisted Testing Workflow

### ⛔ B.0 — Model Introspection (MANDATORY — MUST EXECUTE FIRST)

Before generating ANY test definition or DAX query, read ALL TMDL files from disk to build the **Model Object Registry**.

**Agent Actions (ALL MANDATORY)**:
1. **List** `<ProjectName>/PBIP/<ProjectName>.SemanticModel/definition/tables/*.tmdl`
2. **Read** each table TMDL: extract exact `table` name, `column` names, `dataType`, `isHidden`, `sourceColumn`
3. **Read** `_Measures.tmdl`: extract exact `measure` names, `displayFolder`, `formatString`
4. **Read** `relationships.tmdl`: extract all relationship pairs and cardinality
5. **Build** internal Model Object Registry — used as source of truth for ALL subsequent DAX queries

**CRITICAL RULES**:
- ⛔ NEVER assume column names — ALWAYS read from TMDL
- ⛔ `Table[ColumnName]` in DAX must exactly match the TMDL `column` declaration (PascalCase, NO spaces)
- ⛔ `[Measure Name]` in DAX must exactly match the TMDL `measure` declaration (natural language with spaces)
- ✅ Cross-reference `.github/references/naming-conventions.md` for PascalCase vs natural language rules

**Root cause context**: Previous runs generated `Dim_Area[Area Name]` (assumed spaces) instead of `Dim_Area[AreaName]` (actual TMDL name), causing 100% failure on dimensional tests.

**STOP** — Do NOT proceed to B.1 until Model Object Registry is built.

---

### B.1 — Requirements & Measures Analysis

1. Read functional specification from `<ProjectName>/spec/<spec_file>.md`
2. Extract KPIs, measures, and business rules
3. Map specification requirements to measures in the Model Object Registry
4. Identify critical test scenarios: base aggregations, time intelligence, derived calculations, edge cases
5. Cross-validate all referenced objects exist in the registry
6. Classify the model as either `parameterized_fiscal_year` or `fixed_fiscal_year`
7. For any edge-case test that depends on `ISINSCOPE`, `HASONEVALUE`, or row suppression, design the DAX query to reproduce visual row context using `SUMMARIZECOLUMNS` instead of only slicer-style `CALCULATE` filters

---

### B.2 — Generate Test Definitions

Save to `<ProjectName>/tests/tests_definition.json`.

**CRITICAL**: All `daxQuery` values MUST use names from the Model Object Registry. Column names = PascalCase (no spaces). Measure names = natural language (with spaces).

**CRITICAL**: Every test definition MUST include a machine-readable assertion payload. Narrative text in `expectedBehavior` is not enough.

**CRITICAL expected-value calibration rule**:
- Do NOT use mockup card labels or single-slice visual values as expected totals.
- Derive expected values from reproducible calculations on source CSV data (same filter granularity as the DAX query).
- In `expectedBehavior`, document the exact aggregation scope used for expected-value computation.

**Required assertion fields per test**:
- `assertionType`: one of `numeric_tolerance`, `exact`, `blank`, `row_numeric_tolerance`
- `expectedValue` for scalar assertions, or `expectedRow` for one-row multi-column assertions
- `tolerance` for numeric comparisons

The shared runner supports legacy definitions, but legacy execution-only tests MUST be treated as technical debt and eliminated from new plans.

**Correct vs incorrect DAX references**:
- ✅ `Dim_Area[AreaName]` / ❌ `Dim_Area[Area Name]`
- ✅ `Dim_Customer[CustomerName]` / ❌ `Dim_Customer[Customer Name]`
- ✅ `[Sales Amount FYTD]` (measure = natural language with spaces)

**Required test suites — minimum coverage**:

| Suite | Priority | Tests to include |
|---|---|---|
| TS01 Base Aggregations | HIGH | SUM measures vs CSV totals, count measures |
| TS02 Time Intelligence | CRITICAL | If fiscal start is parameterized: FYTD with ≥4 FY start scenarios: Jan, Apr, Jul, Oct. If fiscal start is fixed in DAX: use ≥4 representative boundary dates across the active fiscal year |
| TS03 Derived Calculations | HIGH | Variance measures, percentage measures (DIVIDE check) |
| TS04 Edge Cases | MEDIUM | Zero denominator → BLANK, no-data filters → BLANK, visual-row-context checks for `ISINSCOPE` or similar logic |
| TS05 Field Parameters | CRITICAL when used | Dimension switch coverage, measure switch coverage, default selections, compatible filter propagation |

For detailed test case examples per suite, see `.github/skills/functional-testing/references/functional-testing-methodology.md`.

**Schema**:

```json
{
  "projectName": "<FROM MODEL>",
  "modelVersion": "1.0.0",
  "generatedDate": "<CURRENT_DATE>",
  "modelObjectRegistry": {
    "tables": ["<from TMDL>"],
    "columns": { "<TableName>": ["<from TMDL column declarations>"] },
    "measures": ["<from _Measures.tmdl measure declarations>"]
  },
  "testSuites": [
    {
      "suiteId": "TS01",
      "suiteName": "Base Aggregations",
      "priority": "HIGH",
      "tests": [
        {
          "testId": "T01.01",
          "testName": "[Sales Amount] total",
          "measureName": "Sales Amount",
          "testType": "Unit Test",
          "daxQuery": "EVALUATE { [Sales Amount] }",
          "assertionType": "numeric_tolerance",
          "expectedValue": 1234567.89,
          "tolerance": 0.01,
          "expectedBehavior": "Matches SUM of source CSV column within 0.01 tolerance",
          "validationMethod": "Compare with pandas CSV total",
          "passThreshold": "Difference < 0.01"
        }
      ]
    }
  ]
}
```

**STOP** — Present `tests_definition.json` summary and await user approval before B.3.

---

### B.3 — Automated Test Execution

**Prerequisites**: Power BI Desktop open with PBIP project loaded.

**Mandatory preflight**:
1. Detect the active Analysis Services port from `AnalysisServicesWorkspaces/**/msmdsrv.port.txt`
2. If multiple workspaces exist, prefer the most recently modified workspace and print the resolved file path
3. Run a smoke-test query such as `EVALUATE ROW("test", 1)` before the full suite
4. If auto-detection fails, rerun with `--port <port>` instead of continuing with guesswork

Detect Analysis Services port:
```powershell
powershell -ExecutionPolicy Bypass -File .github/skills/functional-testing/scripts/get_pbi_desktop_port.ps1
```

Machine-readable output (for automation):
```powershell
powershell -ExecutionPolicy Bypass -File .github/skills/functional-testing/scripts/get_pbi_desktop_port.ps1 -AsJson
```

Invoke the universal test runner:
```powershell
python .github/skills/functional-testing/scripts/run_tests.py <ProjectName> --port <port> --verbose
```

For Python/pyodbc implementation details, cross-validation logic, and CSV comparison method, see `.github/skills/functional-testing/references/functional-testing-methodology.md`.

---

### B.4 — Generate Test Execution Report

Save to `<ProjectName>/tests/tests_execution.md`.

Include per test: test ID, name, status (✅⚠️❌), expected vs actual values, query execution time, fix recommendations for failures.
If the test uses row-context simulation, explicitly state that in the report so reviewers do not misread the assertion as a plain slicer-filter check.

For the full report template and performance summary format, see `.github/skills/functional-testing/references/functional-testing-methodology.md`.

---

### B.5 — User Review & Action

- **All PASS** → Proceed
- **Warnings** → review recommendations, decide if optimization needed
- **Failures** → apply fix, re-run affected tests, update report

---

## Completion Checklist

Before marking Step 07 complete:

- [ ] Model Object Registry built from TMDL (B.0 completed)
- [ ] `tests_definition.json` generated and user-approved
- [ ] Base aggregation measures validated against CSV source totals
- [ ] Fiscal tests aligned with the model design: parameterized FY-start models cover Jan, Apr, Jul, Oct; fixed-fiscal-year models cover at least four representative fiscal-boundary dates
- [ ] DIVIDE() zero-denominator verified → returns BLANK, not error
- [ ] Dimensional filter propagation and relationship paths validated
- [ ] Any `ISINSCOPE` or row-suppression measure is tested in visual-equivalent row context, not only through slicer filters
- [ ] If field parameters are used, every selectable dimension/measure combination is tested in at least one representative visual context
- [ ] If measure parameters are used, shared context dimensions are validated to produce sensible results for all selectable measures
- [ ] Performance benchmarks documented
- [ ] All failures triaged and resolved (or risk-accepted by user)
- [ ] `tests_execution.md` saved to `<ProjectName>/tests/`

Save all test artifacts to disk.

---

## References

- **Functional Testing Methodology**: `.github/skills/functional-testing/references/functional-testing-methodology.md` — Mode A manual test suites (Phase 1–6), full report template, Python implementation notes, anti-patterns
- **DAX Patterns**: `.github/skills/dax-development/references/dax-patterns.md`
- **DAX Optimization**: `.github/skills/dax-development/references/dax-optimization-framework.md`
- **Naming Conventions**: `.github/references/naming-conventions.md`
- **BPA Rules**: `.github/skills/code-review/references/bpa-rules-reference.md`
- **Test Runner**: `.github/skills/functional-testing/scripts/run_tests.py` — Universal automated test execution engine