Update Plan: Active IEP Measure + Research Notes

Baseline measure reviewed:

Active IEP Students =
CALCULATE(
  DISTINCTCOUNT('Student Data Export (3)'[SSDID]),
  'Student Data Export (3)'[iepStatus] = "ACTIVE"
)

1) Recommended Updates (Prioritized)

1. Rename table for maintainability (optional but recommended) to Students.
2. Normalize status in Power Query (TRIM/UPPER) to avoid hidden mismatches like active, ACTIVE .
3. Exclude blank student IDs to avoid accidental blank-member counting.
4. Add strict variant for public reporting with explicit blank exclusion.
5. Add data-quality audit measures (blank IDs, unknown statuses).
6. Add public suppression wrappers for subgroup visuals (n < 10).
7. Add validation visuals to reconcile totals by District/School.

2) Research-backed Notes

• DISTINCTCOUNT counts BLANK as a valid distinct value; use blank guards when needed.
• DISTINCTCOUNTNOBLANK is available when you want blank exclusion behavior.
• CALCULATE with boolean filter arguments is preferred for simple column filters.
• Microsoft guidance: avoid unnecessary FILTER() in CALCULATE filter args when boolean expressions can do the job.

3) Updated Measure Set (Copy/Paste)

3.1 Canonical production measure

Active IEP Students =
CALCULATE(
    DISTINCTCOUNT(Students[SSDID]),
    Students[iepStatus] = "ACTIVE",
    NOT ISBLANK(Students[SSDID])
)

3.2 If table name must stay as-is

Active IEP Students =
CALCULATE(
    DISTINCTCOUNT('Student Data Export (3)'[SSDID]),
    'Student Data Export (3)'[iepStatus] = "ACTIVE",
    NOT ISBLANK('Student Data Export (3)'[SSDID])
)

3.3 Optional strict no-blank variant (if supported in your model)

Active IEP Students (No Blank) =
CALCULATE(
    DISTINCTCOUNTNOBLANK(Students[SSDID]),
    Students[iepStatus] = "ACTIVE"
)

3.4 Data quality checks

Blank SSDID Rows =
CALCULATE(COUNTROWS(Students), ISBLANK(Students[SSDID]))

Active Status Rows =
CALCULATE(COUNTROWS(Students), Students[iepStatus] = "ACTIVE")

Unknown IEP Status Rows =
CALCULATE(
  COUNTROWS(Students),
  ISBLANK(Students[iepStatus]) ||
  NOT (Students[iepStatus] IN {"ACTIVE","INACTIVE","CLOSED"})
)

4) Step-by-step Implementation

1. Power Query: normalize iepStatus with TRIM+UPPER.
2. Ensure SSDID type is stable (text or whole number, not mixed).
3. Create updated measure in your Measures table.
4. Create validation matrix: District, School, Active IEP Students.
5. Add audit cards: Blank SSDID Rows, Unknown IEP Status Rows.
6. Swap visuals from old measure to updated measure.
7. Republish and compare with prior totals (variance should be explainable).

5) Sources consulted

• Microsoft Learn — CALCULATE (DAX)
• Microsoft Learn — DISTINCTCOUNT / DISTINCTCOUNTNOBLANK (DAX)
• Microsoft Learn best practice: avoid FILTER as filter argument when unnecessary