functional-testing

name	functional-testing
description	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".
user-invocable	true

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:

{
  "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 -ExecutionPolicy Bypass -File .github/skills/functional-testing/scripts/get_pbi_desktop_port.ps1

Machine-readable output (for automation):

powershell -ExecutionPolicy Bypass -File .github/skills/functional-testing/scripts/get_pbi_desktop_port.ps1 -AsJson

Invoke the universal test runner:

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