Assign patient to office based on most recent office visited. Struggling with measure.

[pharmboyrx]

Hello,

I work for a medical practice with multiple offices. Sometimes a patient will transfer their care from one office to another. I want to be able to count the people in care at an office (1 visit in the last year), but only if that is the office they most recently received care from. Right now the measure is counting patients who received care from an office in the past year even if the most recent visit was at a different office.

fact-EncountersEBO8 is a table with all patient encounters. dim-Date1 is the date table. dim-Offices is the office table. I've used SELECTEDVALUE in the second table because the 'dim-Offices'[OfficeEnc] is in the slicer on the report and I want to use the slicer to control the measure output.

Any help would be appreciated. I'm stumped.

PtCountLastOffice = 

VAR _LastVisitperPatient =

CALCULATETABLE(

   ADDCOLUMNS(

      VALUES('fact-EncountersEBO8'[PatientID]),

      "@LatestDate", CALCULATE(MAX('fact-EncountersEBO8'[AppointmentDate]))

    ),

   FILTER(ALL('dim-Date1'),

      'dim-Date1'[Dates] > MAX('dim-Date1'[Dates]) - 365 &&+

      'dim-Date1'[Dates] <= MAX('dim-Date1'[Dates]))

   )

VAR _LastOfficeVisit =

CALCULATETABLE(

   VALUES('fact-EncountersEBO8'[PatientID]),

   'fact-EncountersEBO8'[AppointmentFacilityName] = SELECTEDVALUE('dim-Offices'[OfficeEnc]),

   FILTER(ALL('dim-Date1'),

      'dim-Date1'[Dates] > MAX('dim-Date1'[Dates]) - 365 &&

      'dim-Date1'[Dates] <= MAX('dim-Date1'[Dates])),

   TREATAS(_LastVisitperPatient,'fact-EncountersEBO8'[PatientID],'fact-EncountersEBO8'[AppointmentDate])

   )

RETURN

COUNTROWS(_LastOfficeVisit)

[v-zhangti]

Hi, @pharmboyrx 

You can try the following methods. I have simulated some data that I hope will fit your situation. I understand that you want to count the number of people who have been seen more than once in the past year in the same office.

Measure:

Count PatientID =
CALCULATE (
    COUNT ( 'fact-EncountersEBO8'[PatientID] ),
    FILTER (
        ALL ( 'fact-EncountersEBO8' ),
        [AppointmentFacilityName] = SELECTEDVALUE ( 'dim-Offices'[OfficeEnc] )
            && [PatientID] = SELECTEDVALUE ( 'fact-EncountersEBO8'[PatientID] )
            && [AppointmentDate]
                > MAX ( 'dim-Date1'[Dates] ) - 365
            && [AppointmentDate] <= MAX ( 'dim-Date1'[Dates] )
    )
)

More than once =
CALCULATE (
    DISTINCTCOUNT ( 'fact-EncountersEBO8'[PatientID] ),
    FILTER (
        ALLSELECTED ( 'fact-EncountersEBO8'[PatientID] ),
        [Count PatientID] >= 2
    )
)

Please check the attached data, does it match the output you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[pharmboyrx]

Thanks for this, but what I'm really trying to do is count the number of patients in care at an office who have had 1 vist in the last 1 year. However, I only want them counted by the office they were most recently seen at. 

So if an patient was seen today at Office 3, but then seen 10/1/22 at Office 5, I would want them counted as part of Office 5's population in October, but in Office 3's population before that.

I edited your file so that Patient 3 has visits in multiple offices. The problem I'm having is that they continue being counted in the previous office even though they've gone to a new office. In this case Patient 4 is counted in Office 3 starting in June 2022, but is still being counted in Office 5 as well. I want them to stop being counted in the old office when they go to a new office.

Thanks for the assistance.

PBIX File 

[Nathaniel_C]

Hi @pharmboyrx ,

If I understand you correctly, here is one way to do this.
Create a calculated column in your fact table to note the last visit per ID.

Then create a measure that counts the rows per the selected value.
I will let you add the date filter to this - you can use something like Today()-365.

Please see the code and pictures below.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Last visit = 
var _curPID = [PID]
var _curDate = [Date]
var _maxDate = CALCULATE(MAX('PatientVisit'[Date]),FILTER('PatientVisit','PatientVisit'[PID]=_curPID))
var _lastVisit = If(_maxDate=_curDate,'PatientVisit'[Office],"")
return _lastVisit
================================
Total Visits = 
var _OfficeSelected = AllSelected(TableOfc[Office])

return
CALCULATE(COUNTROWS(PatientVisit),FILTER(PatientVisit,PatientVisit[Last visit]= _OfficeSelected))