Help with DAX

[shug]

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 would be marked as 1 whereas if the examination happened on 01/02/2023 and 'cosmetic' on the 20/03/2023, it would be marked as 0. A patient may also have a future examination and future cosmetic so it should pick up those as well.

Patientkey is in 'treatments' table but 'patient name'  is from the 'patients' table. Rest of the columns are in 'Treatments'

PatientKey	Completed Date	Patient Name	Treatment Category	No. of Items
1	11/01/2023 12:20	A	Examinations	1
1	14/02/2023 13:18	A	Cosmetic	1
1	07/03/2023 15:59	A	Examinations	1
1	29/03/2023 15:50	A	Treatment Stages	1
1	29/03/2023 15:50	A	Restorative	1
1	17/04/2023 15:54	A	Prosthodontics	1
1	17/04/2023 15:54	A	Prosthodontics	1
2	12/01/2023 15:42	B	Orthodontics	1
2	12/01/2023 15:44	B	Orthodontics	1
2	01/03/2023 16:34	B	Examinations	1
2	01/03/2023 16:39	B	Miscellaneous	1
2	01/03/2023 16:39	B	Orthodontics	1
2	01/03/2023 16:39	B	Orthodontics	1
2	02/03/2023 15:16	B	Miscellaneous	1
2	02/03/2023 17:03	B	Miscellaneous	1
2	09/03/2023 14:37	B	Examinations	1
2	03/04/2023 16:58	B	Miscellaneous	1
2	25/04/2023 09:47	B	Examinations	1
2	28/06/2023 13:00	B	Examinations	1
2	28/06/2023 13:00	B	Non-Clinical Services	1
2	30/06/2023 13:09	B	Examinations	1
2	30/06/2023 13:09	B	Treatment Stages	1
2	14/07/2023 12:51	B	Cosmetic	1

[rajendraongole1]

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])
)

last apply the flag 0 or 1 condition on table. as below one more measure create it.

PatientCompletedCosmeticWithin4Weeks =
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!!