DAX Fundamentals

💡 Key concepts

• Measures calculate at query time in the current filter context. Use them for nearly all numbers on a report. Calculated columns calculate row by row at refresh and are stored. Use them for slicing and grouping attributes.
• Filter context: The set of filters from slicers, rows/columns of a visual, and page/report filters.
• Row context: "The current row", present in calculated columns and iterators (SUMX, FILTER).
• CALCULATE: Changes filter context. The most important DAX function.

  BCWP = SUM ( fact_EVM[BCWP] )
  ACWP = SUM ( fact_EVM[ACWP] )
  BCWS = SUM ( fact_EVM[BCWS] )
  
  CPI = DIVIDE ( [BCWP], [ACWP] )
  SPI = DIVIDE ( [BCWP], [BCWS] )
  CV  = [BCWP] - [ACWP]
  SV  = [BCWP] - [BCWS]
  
  RDT&E Actual Obligations =
      CALCULATE ( SUM ( fact_Budget[ActualObligation] ), dim_Appropriation[Appropriation] = "RDT&E" )
  
  Late Milestones =
      CALCULATE (
          COUNTROWS ( fact_Milestones ),
          ISBLANK ( fact_Milestones[ActualDate] ),
          fact_Milestones[ForecastDate] < TODAY ()
      )
  

• Iterators: SUMX(table, expression) evaluates row by row, then sums. Example: weighted values, risk score = SUMX(fact_Risks, fact_Risks[Likelihood] * fact_Risks[Consequence]).
• VAR / RETURN: Name intermediate results (LET for DAX).
• Measure tables and formatting: Put measures in a dedicated _Measures table, set formats (currency, 0.00 for indices), and use display folders (EVM, Budget, Schedule).

🛠 Hands-on exercise: Core Measure Library

Data: the PEO-FS star from 2.3. Create and format: BCWS, BCWP, ACWP, CV, SV, CPI, SPI, CV %, SV %, Planned Obligation, Actual Obligation, Obligation Rate, Expenditure Rate, Milestone Count, Late Milestones, Avg Slip Days (milestones), Open Risks, High Risks (score ≥ 15). Test each in a matrix by Portfolio → Program, and spot-check two programs against your Phase 1 Excel numbers.

📦 Mini-project: Measure vs. Column Kata

Implement "Milestone Status" two ways: (a) as a calculated column on fact_Milestones, (b) as measures counting each status. Write a paragraph on which belongs where (status as a slicer attribute = column; counts = measures). Then build "SlipBucket" (0, 1–30, 31–90, >90 days) as a column and use it in a bar chart.