Flag Customers who did not come for a visit X times in a row between today and Y days in the past

[StoryDashboard]

Hi,

What might be an easy question seems hard to find out...Let me explain!

I have a table with 3 columns. 

Patient ID | Appointment Date | Visitstatus |

 

A patient can have more Appointments over time. Each appointment can have a status like "SCHEDULED" if the date of the visit is in the future. It has the value "MISSED" when the patient did not come to the appointment he had and 'ATTENDED" if the patient attended the visit.

What I want to know is to flag all patients that missed their appointment 3 times in a row. As you can see in the XSL there are two patients that missed their appointment in the past. 

I know it must be something to do with ranking the appointmentdates for each patient for only missed visits I could need a little help...And of course this will be a new column, not a measure 

In the example you see 1 extra column Flag. Flag should be the answer that I want, giving back a 1 or a 0.

Secondly I want 2 variables that I want to put in the DAX formula to have flexibility. These variables wil come from a table that I will create myself in the model.

First variable : The number of missed visits  (2,3,4,5,6 etc...)  will be extracted using the selectedvalue from a slicer

Second variable : The timeframe (number of days within my formula evaluates). Also feed by a slicervalue (30,60,90,180,360) and kept in a selectedvalue variable.

So for instance the question could be :

List me all patients that missed their visits 3 times in a row for the past 180 days?

or:

List me all patients that missed their visits 2 times in a row for the past 60 days?

[Anonymous · Accepted solution]

Hi

I think you can use earlier in your filters to flag this. It may require a couple of columns though so you can treat it as a flag. 

Firstly create the following calculated column

Consecutive Missed Appt Count = 
CALCULATE( 
         COUNTROWS(Table),
         ALLSELECTED(Table),
         Table[Patient_ID] <= EARLIER([Patient_ID]),
         Table[Appointment Date] <= EARLIER([Appointment Date])
         Table[VISITSTATUS] = 'MISSED'
)

Then you can create another calculated column, that flags when this is more than or equal to three.

Missed 3 Consecutive Appointments Flag =
IF([Consecutive Missed Appt Count] >= 3, 1, 0)

Hope that works/helps.

Thanks

[v-juanli-msft]

Hi @StoryDashboard 

For example, please point if my understanding is correct

patient	date	status	flag
a	1/1/2019	missed	0
a	1/2/2019	missed	0
a	1/3/2019	attended	0
a	1/4/2019	missed	0
a	1/5/2019	missed	0
b	2/1/2019	missed	1
b	2/2/2019	missed	1
b	2/3/2019	scheduled	1
b	2/4/2019	missed	1
b	2/5/2019	missed	1

Best Regards

Maggie

[StoryDashboard]

Hi,

Not exactly. The rule to get a flag is that a patient missed three appointments chronologically. They all should have chronologically have the status missed.

Remember that the status Scheduled is only when the appointment date > today. So in your example there cannot be an appointment which is scheduled on 2/3/2019.

In your example also patient  B meets the requirements to get a flag to miss 3 visits but forget then about the scheduled row which should not be there.

[JohanvdBrink · Accepted solution]

Hi @StoryDashboard ,

I think I have found a working solution. It took me some hours and some red wine, but it seems this will work. The result looks like this:

I created a sequence number using the following DAX-formula:

Sequence = 
VAR CurrentDate = visitdata[APPOINTMENT DATE]
RETURN COUNTROWS (
    FILTER (
        CALCULATETABLE (
            visitdata;
            ALLEXCEPT ( visitdata; visitdata[PATIENT_ID] )
        );
        visitdata[APPOINTMENT DATE] < CurrentDate
          || ( visitdata[APPOINTMENT DATE] = CurrentDate 
         )
    )
)

Then based on that sequence I am able to use LOOKUPS to find the previous (StatusMin1) and "pre-previous" (StatusMin2) value for that patient_id:

StatusMin1 = LOOKUPVALUE(visitdata[VISITSTATUS];visitdata[PATIENT_ID];'visitdata'[PATIENT_ID];visitdata[Sequence];'visitdata'[Sequence]-1)

StatusMin2 = LOOKUPVALUE(visitdata[VISITSTATUS];visitdata[PATIENT_ID];'visitdata'[PATIENT_ID];visitdata[Sequence];'visitdata'[Sequence]-2)

I also made a variation of the sequence number that only counts status "MISSED" (SequenceMissed):

SequenceMissed = 
VAR CurrentDate = visitdata[APPOINTMENT DATE]
VAR CurrentStatus = visitdata[VISITSTATUS]
RETURN COUNTROWS (
    FILTER (
        CALCULATETABLE (
            visitdata;
            ALLEXCEPT ( visitdata; visitdata[PATIENT_ID] )
        );
        (visitdata[APPOINTMENT DATE] < CurrentDate
          || ( visitdata[APPOINTMENT DATE] = CurrentDate)) && visitdata[VISITSTATUS] = "MISSED" && CurrentStatus = "MISSED"
    )
)

Now I use patientid and sequence to be able to find the previous and pre-previous row for every row that adheres to these conditions: SequenceMissed = 3, status of previous row = "MISSED" and status of pre-previous row = "MISSED" as well.

HasThreeMissedAppointments = 
VAR CurrentPatientID = visitdata[PATIENT_ID]
VAR CurrentSequence = visitdata[Sequence]
RETURN 
CALCULATE(COUNT(visitdata[Sequence]);FILTER(visitdata;visitdata[SequenceMissed] = 3 && visitdata[StatusMin1] = "MISSED" && visitdata[StatusMin2] = "MISSED" && visitdata[PATIENT_ID] = CurrentPatientID && visitdata[Sequence] = CurrentSequence))

 The right table only shows patient IDs with 3 consecutive missing appointments. 

Johan

[Anonymous · Accepted solution]

Hi

I think you can use earlier in your filters to flag this. It may require a couple of columns though so you can treat it as a flag. 

Firstly create the following calculated column

Consecutive Missed Appt Count = 
CALCULATE( 
         COUNTROWS(Table),
         ALLSELECTED(Table),
         Table[Patient_ID] <= EARLIER([Patient_ID]),
         Table[Appointment Date] <= EARLIER([Appointment Date])
         Table[VISITSTATUS] = 'MISSED'
)

Then you can create another calculated column, that flags when this is more than or equal to three.

Missed 3 Consecutive Appointments Flag =
IF([Consecutive Missed Appt Count] >= 3, 1, 0)

Hope that works/helps.

Thanks

[StoryDashboard]

Hi,

Just brought in your DAX formula which now looks like this :

Consecutive Missed Appt Count =

CALCULATE(

          COUNTROWS(Query1);

          ALLSELECTED(Query1);

          Query1[PATIENT_ID]       <= EARLIER([PATIENT_ID]);

          Query1[APPOINTMENT DATE] <= EARLIER([APPOINTMENT DATE]);

          Query1[VISITSTATUS]= "MISSED"

          )

When I look at the data it is not yet correct. It looks like the output is not reset correctly?!

By the way, forget about the 3dr column that i created...

My output look like as above

[Anonymous]

Apologies, I think the missed filter was in the wrong location.

Please find what I think is the corrected code below.

CALCULATE(
          COUNTROWS(Query1);
          FILTER(ALLSELECTED(Query1), [VISITSTATUS]="MISSED");
          Query1[PATIENT_ID]       <= EARLIER([PATIENT_ID]);
          Query1[APPOINTMENT DATE] <= EARLIER([APPOINTMENT DATE]);
          )

[StoryDashboard]

Hi,

This also is not yet the solution. What the DAX is doing well is doing it right for the first Patient. At the next patient the output of ther formula is not correct.

What it should do it that if only missed visits are in 3 in a row then mark this third row of the patient with a 3. The second time he missed the visit could be marked with a 2 and the first time he misses could be marked with a 1. But when he misses 2 appointment and after that he attended one and after that he again misses a visit then the counter should reset.

Of course I am able to have this table sorted in the right way but thast was already an assumption.

I think we are almost there!