Identify no's by value on subsequent row

[bigrods]

Hi,

Not sure how to describe this accurately:

I have a table of Patient appointments; I'd like to identify the no. of patients where:

They have an appointment of Rota Type = "Telemedicine" AND their next appointment by Appointment Date (of any Rota Type) has Follow Up = TRUE

In the example below, I'd expect the value of 1 to be returned, as Patient ID 123456 has a Telemedicine appt on the 10th, then the next appointment they have on 12th is Follow Up = TRUE - even though the Rota Type is different.

If the Follow Up is FALSE for the subsequent appointment, I do not want to include this.

Also, the Follow Up value for the Telemedicine appointment could be either TRUE or FALSE; I am not interested in that.

Patient ID	Appt Date	Rota Type	Follow Up
123456	10-Mar-2022	Telemedicine	FALSE
987654	10-Mar-2022	Fitting	TRUE
123456	12-Mar-2022	BBV Clinic	TRUE
987654	14-Mar-2022	Fitting	TRUE

Many thanks for any help anyone could give!

[DataInsights · Accepted solution]

@bigrods,

Try this measure:

Number of Patients = 
SUMX (
    VALUES ( PatientAppointment[Patient ID] ),
    VAR vPatientID = PatientAppointment[Patient ID]
    VAR vApptDate =
        MINX (
            FILTER (
                ALLSELECTED ( PatientAppointment ),
                PatientAppointment[Patient ID] = vPatientID
                    && PatientAppointment[Rota Type] = "Telemedicine"
            ),
            PatientAppointment[Appt Date]
        )
    VAR vNextApptDate =
        MINX (
            FILTER (
                ALLSELECTED ( PatientAppointment ),
                PatientAppointment[Patient ID] = vPatientID
                    && PatientAppointment[Appt Date] > vApptDate
                    && NOT ISBLANK ( vApptDate )
            ),
            PatientAppointment[Appt Date]
        )
    RETURN
        IF (
            COUNTROWS (
                FILTER (
                    ALLSELECTED ( PatientAppointment ),
                    PatientAppointment[Patient ID] = vPatientID
                        && PatientAppointment[Appt Date] = vNextApptDate
                        && PatientAppointment[Follow Up] = TRUE
                )
            ),
            1
        )
)

[bigrods]

Hi @DataInsights - sorry ofr the late reply, thanks so much this has worked! I needed to do a bit of transformations as some of my rota names were "Telemmed", "Tele Meds" "Telemedicine" - I initially tried a Containsstring in my filter and the query ground to a halt! 

But I added a conditional column and all is good - thanks again