Forum Discussion

mihaita_baro's avatar
mihaita_baro
Helper II
4 years ago

KPI as calculated column

Hi guys,

 

I have a table called "Appointment" where i keep all the appointment/reviews a patient had and i need to calculate 3 milestones / KPI's for each patient. It can be through a new calculated column or measure.

 

- first one is KPI4 ( column in green ) which i have calculated,  no need for this one and is based on the following

First Review Date when client's
- Session Type is one of the following: Treatment; Assessment and Treatment; Review and Treatment;

2,3,5 from Appoiment.apptype
And
- Attendance Status is one of the following: On time/ahead of staff; Late, was seen;

 

- Second one is called KPI5 and have the following criteria

 

KPI5 date = Sign Off Date . If there is a sign off date
Clients must have at least 2 reviews meeting the following criteria:
- Session Type is one of the following: Treatment; Assessment and Treatment; Review and Treatment;
And
- Attendance Status is one of the following: On time/ahead of staff; Late, was seen; or Blank (has not been filled in on DCRS);

 

basically is at least 2 sessions eligible for KPI4 but with a sign off date 

 

I have done something here but it doesn't calculate properly

 

"

KPI5 FTB Eligible =
VAR a =
    IF (
        COUNTX ( FILTER ( ALL ( 'Appointment_FTB' ), [clientId] = EARLIER ( 'Appointment_FTB'[clientId] ) && 'Appointment_FTB'[assessmentsId] =EARLIER('Appointment_FTB'[assessmentsId]) ), [clientId] ) >= 2,
        1
    )
RETURN
    IF (
        a = 1
            && 'Appointment_FTB'[sessionType]
                    IN { "Assessment and Treatment", "Treatment", "Review and Treatment"}
                    && 'Appointment_FTB'[attendedStatus] IN { "On time / ahead of staff", "Late, was seen" } && 'Appointment_FTB'[SignOffDate]<>BLANK(),
        1
    )"

 

 

- Third one is KPI6 and have the following criteria.

 

Must meet criteria for KPI5. In addition:
Clients must score revgad7anxiety_Result_raw>7 or revphq9depression_Result_raw>9 at Initial Review and
score revgad7anxiety_Result_raw<8 and revphq9depression_Result_raw<10 at Final Review (last review being the latest one with PHQ and GAD assessment); 

 

The output should be something like in the below screenshot. Not necesarily to be done via calculated column, can be a measure as well

Tips and Tricks
Tutorial Requests

2 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    3 years ago

    Hi mihaita_baro 

    Thanks for reaching out to us.

    >> The output should be something like in the below screenshot. Not necesarily to be done via calculated column, can be a measure as well

    could you provide the calculation formula so that we can convert it to dax code? thanks. if it involves other table, please provide its sample data.

     

    Best Regards,

    Community Support Team _Tang

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

    • mihaita_baro's avatar
      mihaita_baro
      Helper II
      3 years ago

      hi v-xiaotang 

       

      So for KPI5 the formula is based on  Sign Off Date . If there is a sign off date and the Clients must have at least 2 reviews where Session Type is one of the following: Treatment; Assessment and Treatment; Review and Treatment;
      And
      - Attendance Status is one of the following: On time/ahead of staff; Late, was seen; or Blank .

       

      WHile  KPI6 must have KPI5 and in addition

      Clients must score revgad7anxiety_Result_raw>7 or revphq9depression_Result_raw>9 at Initial Review (first appointment )  and
      score revgad7anxiety_Result_raw<8 and revphq9depression_Result_raw<10 at Final Review (last review being the latest one with PHQ and GAD assessment);