Detailed Step-by-Step: Build an Attractive Power BI Dashboard from /AAW/Dashboard

Source folder: smb://192.168.1.165/AAW/Dashboard (mounted as /Volumes/AAW/Dashboard)

Last Updated: Friday, 02/27/2026

Step 1) Prepare your local files

1. Open Finder → Go to folder: /Volumes/AAW/Dashboard.
2. Confirm these files exist:
   • Student Data Export (3).csv
   • DISABILITY_CODE_MAP.csv
   • ETHNICITY_CODE_MAP.csv
   • LRE_PLACEMENT_CODE_MAP.csv
3. Copy them into a stable local working folder (optional but recommended), e.g.: ~/Documents/PowerBI/AAW-Dashboard/.

Step 2) Create a new Power BI report

1. Open Power BI Desktop.
2. Create a new report.
3. Save immediately as: AAW_Public_Dashboard.pbix.

Step 3) Load data files

1. Home → Get Data → Text/CSV.
2. Load Student Data Export (3).csv.
3. Load each mapping CSV:
   • DISABILITY_CODE_MAP.csv
   • ETHNICITY_CODE_MAP.csv
   • LRE_PLACEMENT_CODE_MAP.csv
4. Click Transform Data to open Power Query.

Step 4) Clean and type fields in Power Query

4.1 Student table cleanup

• Ensure SSDID is text or whole number consistently.
• Convert date columns to Date type: DOB, iepDueDate, ReevalDueDate.
• Convert numeric text to decimal/whole number: TotalIEPMinutes, RelatedServiceMinutes, LRE, SESPercentage.
• Optional rename for clarity:
  • PrimaryDis → PrimaryDisabilityCode
  • SecondDis → SecondaryDisabilityCode
  • ThirdDis → TertiaryDisabilityCode
  • AttendingSchooldId keep note: spelling appears source typo

4.2 Mapping joins

• Merge Student table with disability map on PrimaryDis.
• Merge with ethnicity map on Ethnicity.
• Merge with LRE map on LRE (cast to matching type first).
• Expand useful display columns (e.g., short labels + expanded labels).

4.3 Load model

• Click Close & Apply.

Step 5) Model setup and relationships

1. Open Model view.
2. Verify one-to-many from each map table to student table.
3. Single direction filter from maps to student fact table.
4. Hide technical columns not needed in visuals.

Step 6) Add KPI DAX measures

Create a new measure table called Measures, then paste/adapt these core measures (from your local DAX spec):

Total Students = DISTINCTCOUNT(Students[SSDID])

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

IEP Service Rate % = DIVIDE([Active IEP Students], [Total Students])

Inclusion 80+ Count =
CALCULATE(DISTINCTCOUNT(Students[SSDID]), Students[iepStatus]="ACTIVE", Students[LRE]="1100")

Inclusion 80+ Rate % = DIVIDE([Inclusion 80+ Count], [Active IEP Students])

Mostly Separate (<40) Count =
CALCULATE(DISTINCTCOUNT(Students[SSDID]), Students[iepStatus]="ACTIVE", Students[LRE]="1301")

Mostly Separate (<40) Rate % = DIVIDE([Mostly Separate (<40) Count], [Active IEP Students])

Add due-date and transition/ESY/related-services measures from POWERBI_DAX_MEASURES.md.

Public suppression rule (required)

Suppression Threshold = 10

Inclusion 80+ Rate % (Public) =
VAR n = [Active IEP Students]
RETURN IF(n < [Suppression Threshold], BLANK(), [Inclusion 80+ Rate %])

Step 7) Build the dashboard layout (attractive one-page design)

Follow your design plan in this exact section order:

1. Header band: title + last refresh + compact slicers (District, School, Grade, Ethnicity, Disability).
2. KPI card row (6 cards):
   • Active IEP Students
   • IEP Service Rate %
   • Inclusion 80+ Rate %
   • Mostly Separate (<40) Rate %
   • Transition Services Rate %
   • ESY Eligibility Rate %
3. Placement mix section: 100% stacked bar + donut by LRE categories.
4. Supports section: clustered bars by grade + average minutes cards.
5. Timelines section: IEP/Reeval due counts by school with urgency colors.
6. Equity section: matrix by ethnicity with suppression-aware public measures.

Step 8) Styling for a polished look

• Use rounded cards (10–12 px), subtle shadow, high contrast text.
• Color semantics:
  • Green: Inclusion 80%+
  • Blue: neutral primary metrics
  • Amber/Orange: watchlist metrics
  • Red: urgent due-date visuals
• Typography: Segoe UI, large KPI values (28–36 pt), concise labels.
• Avoid internal jargon on visible labels (use parent-friendly language).

Step 9) Add interactions and usability

1. Enable cross-filtering across visuals.
2. Create tooltip pages with plain-language KPI definitions.
3. Add a “Reset filters” button in top-right.
4. Build mobile layout (card-first arrangement).

Step 10) QA checklist before publish

Step 11) Suggested report page set

1. Public Overview
2. School Comparison
3. Disability & Placement Detail
4. Timelines & Caseload Management (internal)