Forum Discussion

mihaita_baro's avatar
mihaita_baro
Helper II
3 years ago
Solved

Calculated KPI based on two other columns

Hi guys,

 

I have a small issue with my KPI6 calculation formula, i don't seems to find a calculation for my KPI6

 

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 

 

And 

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

 

I have created another  two columns to see if initial review and last review qualify for KPI6 based on revgad7anxiety_Result_raw and revphq9depression_Result_raw and the sessions .

 

All i need now is to calculate KPI6 based on these two columns but i don't seems to find a way.

 

The output is the column "KPI6"

 

 

 

Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi mihaita_baro ,

    Please try below steps:

    1. below is my test table

    Table:

    2. add a new column with below dax formula

     

    Column =
VAR cur_clientID = 'Table'[Client ID]
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Client ID] = cur_clientID )
VAR _a =
    SUMX ( tmp, [KPI6 Initial Review] + [KPI6 Last Review] )
RETURN
    IF (
        _a = 2
            && 'Table'[KPI6 Initial Review] = BLANK ()
            && 'Table'[KPI6 Last Review] = 1,
        1,
        BLANK ()
    )

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • claymcooper's avatar
    claymcooper
    Resolver II
    3 years ago

    Can you enlarge the image or provide a sample pbix? I can't read the screenshot provided.

      • claymcooper's avatar
        claymcooper
        Resolver II
        3 years ago

        Thanks. So from your example are you trying to evaluate how many clients meet the requirements for KPI6? In your screenshot the total would equal 4. Correct?

    • mihaita_baro's avatar
      mihaita_baro
      Helper II
      3 years ago

      Hi claymcooper 

       

      That column is the  desired output i wanna calculate.

       

      It can be either a calculated column or a measure that count how many clients have KPI6 Initial Review =1 and KPI6 Last review =1

       

      This is what i came up with but doesn't calculate properly

       

      KPI6 FTB =
      CALCULATE(DISTINCTCOUNT(Appointment_FTB[clientId]),
      KEEPFILTERS(
      FILTER(ALL(Appointment_FTB[KPI6 Initial Review],Appointment_FTB[KPI6 Last Review]),
      Appointment_FTB[KPI6 Initial Review]=1 || Appointment_FTB[KPI6 Last Review]=1
      )
      )
      )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi mihaita_baro ,

    Please try below steps:

    1. below is my test table

    Table:

    2. add a new column with below dax formula

     

    Column =
VAR cur_clientID = 'Table'[Client ID]
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Client ID] = cur_clientID )
VAR _a =
    SUMX ( tmp, [KPI6 Initial Review] + [KPI6 Last Review] )
RETURN
    IF (
        _a = 2
            && 'Table'[KPI6 Initial Review] = BLANK ()
            && 'Table'[KPI6 Last Review] = 1,
        1,
        BLANK ()
    )

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.