Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StoryDashboard
Frequent Visitor

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

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 |

 

 Missed visits.jpg

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 Smiley Very Happy

 

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?

 

 

 

 

 

 

 

 

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

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:

patient 3 missed appointments in a row.png

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

View solution in original post

7 REPLIES 7
v-juanli-msft
Community Support
Community Support

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

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.

 

 

 

 

 

 

 

 

 

 

 

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:

patient 3 missed appointments in a row.png

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
Not applicable

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

Missed visits V2.jpg

 

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 aboveSmiley Indifferent
 
 
 
Anonymous
Not applicable

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

 

 

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!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.