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

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:

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