Dashboard 2 of 4 · Diagnosis Analysis

Diagnosis Analysis — Detailed Report

Power BI page: Diagnosis Analysis  ·  Data view: vw_diagnosis_analysis  ·  Verified against data/databricks_output/exports/vw_diagnosis_analysis.csv

Open Live Dashboard Dashboard 1 All Reports Markdown
1 · Hospital Overview 2 · Diagnosis Analysis 3 · Medication Analysis 4 · Hospital Operations

1. Purpose

The Diagnosis Analysis page explores ICD-coded clinical conditions across admissions — distinguishing primary admitting diagnoses from secondary comorbidities, and linking diagnosis patterns to patient demographics and length of stay.

Questions this page answers: How many diagnosis records exist per patient and admission? What are the most common primary and secondary diagnoses? How do diagnosis volumes vary by age and gender? Which conditions are associated with the longest hospital stays?

2. KPI Cards

Three headline metrics across the top of the page. All values recomputed from the CSV export — exact match with the live dashboard (Power BI rounds 4,506 records to 5K).

100
Diagnosed Patients
✓ verified
4,506
Diagnosis Records
✓ verified (5K in PBI)
275
Primary Diagnoses
✓ verified
CardValueVerifiedDAX
Diagnosed Patients100100DISTINCTCOUNT(patient_id)
Diagnosis Records4,5064,506COUNTROWS(vw_diagnosis_analysis)
Primary Diagnoses275275CALCULATE(COUNTROWS(...), is_primary_diagnosis = TRUE())

Derived: 16.4 diagnosis records per admission on average (4,506 ÷ 275 admissions). Exactly one primary diagnosis per admission (275 primary = 275 admissions). Secondary/comorbidity records account for 93.9% of all diagnosis rows.

3. Slicers

Three slicers on the left panel cross-filter all KPI cards and charts:

SlicerValues
age_group18-34 · 35-49 · 50-64 · 65-79 · 80+
genderF · M
admission_typeAMBULATORY OBSERVATION · DIRECT EMER. · DIRECT OBSERVATION · ELECTIVE · EU OBSERVATION · EW EMER. · OBSERVATION ADMIT · SURGICAL SAME DAY ADMISSION · URGENT

4. Visual Analysis

Visual 1 · Horizontal Bar Chart

Top 10 Primary Diagnoses

Count of rows where is_primary_diagnosis = TRUE, grouped by diagnosis_title.

DiagnosisCount
Coronary atherosclerosis of native coronary artery7
Acute kidney failure, unspecified7
Cerebral aneurysm, nonruptured4
Non-ST elevation (NSTEMI) myocardial infarction4
Aortic valve disorders4
Other postoperative infection4
Abscess of liver3
Acute on chronic systolic heart failure3
Encounter for antineoplastic chemotherapy3
Acute respiratory failure with hypoxia3
Finding: Primary admitting diagnoses are cardiovascular and renal — coronary artery disease and acute kidney failure tie at 7 each (2.5% of admissions). No single primary diagnosis dominates; the top 10 cover only 42 admissions (15.3%).
Visual 2 · Horizontal Bar Chart

Top 10 Diagnoses (All Records)

Total diagnosis record count by diagnosis_title — includes primary and secondary ICD codes.

DiagnosisRecordsShare
Unspecified essential hypertension681.5%
Hyperlipidemia, unspecified571.3%
Acute kidney failure, unspecified561.2%
Other and unspecified hyperlipidemia551.2%
Hypothyroidism, unspecified471.0%
Obesity, unspecified431.0%
Anemia, unspecified410.9%
Long term (current) use of insulin370.8%
Urinary tract infection, site not specified360.8%
Personal history of nicotine dependence350.8%
Finding: Secondary comorbidities tell a different story than primary diagnoses. Chronic cardiometabolic conditions dominate — hypertension (68), hyperlipidemia variants (112 combined), hypothyroidism, obesity, and insulin use reflect a high-burden older adult population. Top 10 conditions account for 479 records (10.6%).
Visual 3 · Clustered Column Chart

Diagnosis Records by age_group and gender

Age GroupFemaleMaleTotalShare
50–646271,2861,91342.5%
65–796586151,27328.3%
80+35721457112.7%
35–4942518561013.5%
18–34115241393.1%
Finding: 50–64 males account for 1,286 records (28.5% of all diagnoses) — the single largest demographic segment. Overall gender split is nearly even (F: 2,182 · M: 2,324). The 50–64 male skew suggests higher admission frequency and/or more coded comorbidities per admission in that cohort.
Visual 4 · Horizontal Bar Chart

Average length_of_stay_days by diagnosis_title

Mean LOS at the diagnosis-record level (admission LOS repeated per ICD row). Sorted by highest average stay.

DiagnosisAvg LOS (days)
Catatonic type schizophrenia, unspecified44.93
Foreign body in larynx44.93
Inhalation and ingestion of other object causing obstruction…44.93
Other alteration of consciousness44.93
Acute systolic (congestive) heart failure34.08
Diverticulosis of large intestine without perforation…34.08
Acute embolism and thrombosis of unspecified deep veins…34.08
Takotsubo syndrome34.08
Benign neoplasm of descending colon34.08
Hypothermia, not associated with low environmental temperature34.08
Finding: Power BI displays these as ~45 days and ~34 days (rounded). Several rare diagnoses share identical LOS values because they co-occur on the same long-stay admissions. High LOS is driven by complex inpatient episodes, not by high-frequency chronic conditions like hypertension.

5. Cross-Cutting Analysis

ICD Version Mix

ICD VersionRecordsShare
ICD-102,31351.3%
ICD-92,19348.7%

MIMIC-IV spans the ICD-9 to ICD-10 transition; both code sets appear in the demo subset.

Diagnosis Volume by Admission Type

Admission TypeDiagnosis RecordsShare
EW EMER.1,85341.1%
OBSERVATION ADMIT92620.6%
URGENT70815.7%
EU OBSERVATION3137.0%
DIRECT EMER.2275.0%

Distribution mirrors admission volume from Dashboard 1 — EW EMER. drives the largest share of coded diagnoses.

Primary vs Secondary Pattern

Each admission carries exactly one primary diagnosis (sequence 1) plus an average of 15.4 secondary codes. This reflects MIMIC's rich ICD coding practice where comorbidities, history codes, and status codes are recorded alongside the admitting condition.

6. DAX Measures

Three measures on this page. Full reference: powerbi/mimic_powerbi_dax_measures.md

Diagnosis Records = COUNTROWS(vw_diagnosis_analysis)

Diagnosed Patients = DISTINCTCOUNT(vw_diagnosis_analysis[patient_id])

Primary Diagnoses =
CALCULATE(
    COUNTROWS(vw_diagnosis_analysis),
    vw_diagnosis_analysis[is_primary_diagnosis] = TRUE()
)

7. Key Takeaways

  1. High coding density — 4,506 diagnosis records across 275 admissions (~16.4 per admission); Power BI rounds to 5K.
  2. Primary = acute/cardiac — coronary disease and acute kidney failure lead admitting diagnoses; no single condition dominates.
  3. Secondary = chronic metabolic — hypertension, hyperlipidemia, hypothyroidism, and obesity dominate comorbidity coding.
  4. 50–64 male skew — largest diagnosis volume segment (1,286 records); likely reflects repeat admissions in high-utilizer patients.
  5. LOS ≠ frequency — longest stays tie to rare acute events (~45 days), not common chronic conditions.
  6. ICD-9/10 mix — nearly even split reflects MIMIC's transition-era admissions.
  7. Demo limitations — 100-patient subset; diagnosis titles resolved from ICD codes in the Gold layer.

8. Verification

import pandas as pd
df = pd.read_csv("data/databricks_output/exports/vw_diagnosis_analysis.csv")
assert df["patient_id"].nunique() == 100
assert len(df) == 4506
assert (df["is_primary_diagnosis"].astype(str).str.lower() == "true").sum() == 275
assert df["admission_id"].nunique() == 275

Output: data/databricks_output/exports/diagnosis_analysis.json