DAX Query Development
Dataset Name: Hospital Management System Dataset
1. Write a DAX measure to calculate Total Appointment Fees from the Appointments
table.
Total Fees = SUM(Appointments[Fees])
2. Create a calculated column to classify patients into Age Groups (Child <18, Adult
18–60, Senior >60).
Age Group =
IF(Patients[Age] < 18, "Child",
IF(Patients[Age] <= 60, "Adult", "Senior"))
3. Write a DAX measure to count the Total Number of Appointments.
Total Appointments = COUNT(Appointments[AppointmentID])
4. Create a measure to calculate Average Treatment Cost from the Treatments table.
Avg Treatment Cost = AVERAGE(Treatments[Cost])
5. Create a calculated column to extract Month from Appointment Date.
Month = FORMAT(Appointments[Date], "MMM")
6. Write a DAX measure to calculate Total Revenue (Fees + Treatment Cost).
Total Revenue =
SUM(Appointments[Fees]) + SUM(Treatments[Cost])
7. Create a measure to calculate Year-to-Date (YTD) Revenue.
YTD Revenue =
TOTALYTD(
[Total Revenue],
Appointments[Date]
)
8. Write a DAX formula using CALCULATE() to find revenue where Fees > 1500.
High Fee Revenue =
CALCULATE(
[Total Revenue],
Appointments[Fees] > 1500
)
9. Create a measure to calculate Total Revenue by Doctor using relationships.
Revenue by Doctor =
CALCULATE(
[Total Revenue],
ALLEXCEPT(Appointments, Appointments[DoctorID])
)
10. Write a DAX measure to calculate Percentage Contribution of each City to Total
Revenue.
City Contribution % =
DIVIDE(
[Total Revenue],
CALCULATE([Total Revenue], ALL(Patients[City]))
) * 100
11. Write a DAX measure to calculate Ranking of Doctors based on Revenue.
Doctor Rank =
RANKX(
ALL(Doctors[DoctorID]),
[Total Revenue],
,
DESC
)
12. Create a measure using ALL() to calculate total revenue ignoring all filters.
Total Revenue All =
CALCULATE(
[Total Revenue],
ALL(Appointments)
)
13. Write a DAX formula to calculate Rolling 6-Month Revenue.
Rolling 6 Month Revenue =
CALCULATE(
[Total Revenue],
DATESINPERIOD(
Appointments[Date],
MAX(Appointments[Date]),
-6,
MONTH
)
)
14. Create a DAX measure to calculate Patient Retention Rate (repeat visits).
Repeat Patients =
CALCULATE(
DISTINCTCOUNT(Appointments[PatientID]),
FILTER(
VALUES(Appointments[PatientID]),
COUNT(Appointments[AppointmentID]) > 1
)
)
Retention Rate =
DIVIDE(
[Repeat Patients],
DISTINCTCOUNT(Appointments[PatientID])
)
15. Write a DAX measure for Same Period Last Year (SPLY) Revenue.
SPLY Revenue =
CALCULATE(
[Total Revenue],
SAMEPERIODLASTYEAR(Appointments[Date])
)