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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mihaita_baro
Helper II
Helper II

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
50b9ca7b-0a23-4fcc-bbb9-f432944cb9a7.jpg

2 REPLIES 2
v-xiaotang
Community Support
Community Support

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.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors