Categories: DAX
Tags:

10 advanced DAX questions with answers for Hospital Patient Treatment Dataset

These questions are meant for Hospital Patient Treatment Dataset we studied earlier. Dataset Here

Here are 10 advanced and meaningful DAX questions with their answers based on the Hospital Patient Treatment Dataset, focusing on Recovery Score and its influencing factors. Each also notes whether it’s a Measure, Calculated Column, or Calculated Table.

1. Which department has the highest average recovery score?

DAX Answer: HighestAvgRecoveryDept = TOPN(1, ADDCOLUMNS( VALUES('Patients'[Department]), "AvgRecovery", AVERAGE('Patients'[Recovery Score]) ), [AvgRecovery], DESC )
  • 🔍 Type: Calculated Table

2. Create a measure to calculate the recovery efficiency: Recovery Score per Day of Hospital Stay

DAX Answer: RecoveryEfficiency = DIVIDE( AVERAGE('Patients'[Recovery Score]), AVERAGE('Patients'[Hospital Stay (Days)]) )
  • 🔍 Type: Measure

✅ Conclusion Based on the Chart:

  1. Gastroenterology has the highest recovery efficiency at 4.6, which is above the average of 4.39.
    • This means patients in this department recover faster per day spent in the hospital.
    • Efficient care protocols or less complex cases could be contributing factors.
  2. Cardiology (4.5) and Neurology (4.4) are also above or close to average, indicating fairly high daily recovery effectiveness.
  3. Pediatrics, Orthopedics, and Oncology each have a recovery efficiency of 4.3, which is slightly below average.
    • These departments may involve longer recovery periods or more complex conditions, leading to slightly lower scores.

📊 What It Means for Stakeholders:

  • Hospital administrators might explore what Gastroenterology is doing differently — shorter stays? better follow-up care?
  • Departments below average might benefit from examining care practices, stay durations, or case complexity.
  • This insight can support process optimization, patient care improvements, and resource allocation.

3. Create a calculated column to categorize Recovery Score

DAX Answer: RecoveryCategory = SWITCH( TRUE(), 'Patients'[Recovery Score] >= 85, "Excellent", 'Patients'[Recovery Score] >= 70, "Good", 'Patients'[Recovery Score] >= 50, "Average", "Poor" )
  • 🔍 Type: Calculated Column

4. Find the average treatment cost for patients with Recovery Score > 80

  • DAX Answer: AvgCostHighRecovery = CALCULATE( AVERAGE('Patients'[Treatment Cost]), 'Patients'[Recovery Score] > 80 )
  • 🔍 Type: Measure

5. Create a measure to find the % of patients with Recovery Score above 75

  • DAX Answer: PercentHighRecovery = DIVIDE( CALCULATE(COUNTROWS('Patients'), 'Patients'[Recovery Score] > 75), COUNTROWS('Patients) )
  • 🔍 Type: Measure

6. Create a measure to count patients whose stay exceeded the average but had below-average recovery

  • DAX Answer: LongStayLowRecoveryCount = CALCULATE( COUNTROWS('Patients'), 'Patients'[Hospital Stay (Days)] > AVERAGE('Patients'[Hospital Stay (Days)]), 'Patients'[Recovery Score] < AVERAGE('Patients'[Recovery Score]) )
  • 🔍 Type: Measure

7. Rank doctors based on their patients’ average recovery score

  • DAX Answer: DoctorRecoveryRank = RANKX( ALL('Patients'[Doctor Name]), CALCULATE(AVERAGE('Patients'[Recovery Score])), , DESC )
  • 🔍 Type: Calculated Column (or use in visual filter)

8. Identify which gender has the highest average recovery score

  • DAX Answer: GenderAvgRecovery = ADDCOLUMNS( VALUES('Patients'[Gender]), "AvgRecovery", AVERAGE('Patients'[Recovery Score]) )
  • 🔍 Type: Calculated Table

9. Calculate correlation between Treatment Cost and Recovery Score

  • DAX Answer: CostRecoveryCorrelation = CORREL('Patients'[Treatment Cost], 'Patients'[Recovery Score])
  • 🔍 Type: Measure

10. Find the department with the lowest average hospital stay for patients who recovered well (score > 85)

DAX Answer: FastRecoveryDepartments = TOPN( 1, ADDCOLUMNS( VALUES('Patients'[Department]), "AvgStay", CALCULATE( AVERAGE('Patients'[Hospital Stay (Days)]), 'Patients'[Recovery Score] > 85 ) ), [AvgStay], ASC )
  • 🔍 Type: Calculated Table