Forum Discussion
Help with DAX
DAX measure to show which patient has completed a 'cosmetic' treatment within 4 weeks of completing an 'examination' treatment. E.g. an examination on the 01/02/2023 and cosmetic on the 10/02/2023...
Hi shug - Try the below calculated columns and measure with flag as patient has completed a 'cosmetic' treatment within 4 weeks of completing an 'examination' treatment., if still issue not resolved.
IsCosmeticWithin4Weeks =
VAR CurrentTreatmentDate = 'Cosmetic'[Completed Date]
VAR CurrentPatientKey = 'Cosmetic'[PatientKey]
VAR ExaminationDates =
FILTER(
'Cosmetic',
'Cosmetic'[PatientKey] = CurrentPatientKey &&
'Cosmetic'[Treatment Category] = "Examinations" &&
'Cosmetic'[Completed Date] <= CurrentTreatmentDate &&
'Cosmetic'[Completed Date] > CurrentTreatmentDate - 28
)
VAR HasRecentExamination = COUNTROWS(ExaminationDates) > 0
RETURN
IF(
'Cosmetic'[Treatment Category] = "Cosmetic" && HasRecentExamination,
1,
0
)
create a aggregated results for each patient use measure:
CompletedCosmeticWithin4Weeks =
CALCULATE(
SUM('Treatments'[IsCosmeticWithin4Weeks]),
ALLEXCEPT('Treatments', 'Treatments'[PatientKey])
)
CALCULATE(
SUM('Treatments'[IsCosmeticWithin4Weeks]),
ALLEXCEPT('Treatments', 'Treatments'[PatientKey])
)
last apply the flag 0 or 1 condition on table. as below one more measure create it.
PatientCompletedCosmeticWithin4Weeks =
IF(
[CompletedCosmeticWithin4Weeks] > 0,
1,
0
)
IF(
[CompletedCosmeticWithin4Weeks] > 0,
1,
0
)
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!