Forum Discussion

amaral_diego's avatar
amaral_diego
Icon for Helper II rankHelper II
1 year ago

Office Attendance Adherence Calculation

In Power BI I have a table called "f_PRINCIPAL", I need to create a DAX code for adherence with the columns "SEMANA" and "POLÍTICA" following the rules below:

First step: Count in the week columns "CONTADOR" that have the options "1" which indicates that the person came to the office and "0" which indicates that the person did not come, according to the column "SEMANA" which in turn has the options "Semana 36, Semana 37, etc., the number of days the person was in the office.

Second step: Identify the number of possible days. To calculate, we take the number of days in the week (the standard number of days in the week is 5) and subtract the days of vacation, leave, and absences (Do not consider absences). Column "MOTIVO".

Third step: Divide the first step by the second step.

Fourth step: Identify the person's policy (Column "POLÍTICA").

Fifth step: If the person's policy is 3 days and the division value is greater than 0.6, it means that the policy was met "Cumprida". If the person's policy is 4 days and the division value is greater than 0.8, it means that the policy was met "Cumprida". If the possible days are zero, the division will always be by zero, so consider the policy met. (For example, if the person is on vacation, they will not have any days).

I've tried with this code below

StatusAderência =
VAR SemanaAtual = 'f_PRINCIPAL'[SEMANA]
VAR SiglaAtual = 'f_PRINCIPAL'[SIGLA]
VAR MotivoAtual = 'f_PRINCIPAL'[MOTIVO]
VAR PoliticaAtual = 'f_PRINCIPAL'[POLÍTICA]
VAR DiasNoEscritorio =
    CALCULATE(
        COUNTROWS('f_PRINCIPAL'),
        FILTER(
            'f_PRINCIPAL',
            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&
            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&
            'f_PRINCIPAL'[CONTADOR] = 1
        )
    )
VAR DiasSemana = 5
VAR DiasAusentes =
    CALCULATE(
        COUNTROWS('f_PRINCIPAL'),
        FILTER(
            'f_PRINCIPAL',
            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&
            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&
            'f_PRINCIPAL'[MOTIVO] IN {"Férias", "Afastamento", "Ausência"}
        )
    )
VAR DiasPossiveis = DiasSemana - DiasAusentes
VAR Aderencia = DIVIDE(DiasNoEscritorio, DiasPossiveis, 0)

RETURN
IF(
    DiasPossiveis = 0,
    "Cumprida",
    SWITCH(
        TRUE(),
        PoliticaAtual = "3" && Aderencia > 0.6, "Cumprida",
        PoliticaAtual = "4" && Aderencia > 0.8, "Cumprida",
        "Não Cumprida"
    )
)


Although, the customer found the compliance percentages to be very low for all levels, and indicated that we will use:
"Number of days of presence / Number of possible days of going to the office"

Cold assist me to apply this on this code?


3 Replies

  • amaral_diego's avatar
    amaral_diego
    Icon for Helper II rankHelper II
    1 year ago

    I'm also using this another code to show on the matrix visual

    AderenciaPolitica =
    VAR TotalCumpriu =
    COUNTROWS(
    FILTER(
    f_PRINCIPAL,
    f_PRINCIPAL[StatusAderência] = "Cumprida"
    )
    )
    VAR TotalRegistros = COUNTROWS(f_PRINCIPAL)
    VAR PercentualCumpriu = DIVIDE(TotalCumpriu, TotalRegistros, 0)
    RETURN
    COALESCE(PercentualCumpriu, 0)

    • amaral_diego's avatar
      amaral_diego
      Icon for Helper II rankHelper II
      1 year ago

      He also mentioned, In the second step I understand it to be correct, in the first step it needs to be the week count with the reason "Presença"

      so I made this code below

      StatusAderencia =
      VAR SemanaAtual = 'f_PRINCIPAL'[SEMANA]
      VAR SiglaAtual = 'f_PRINCIPAL'[SIGLA]
      VAR MotivoAtual = 'f_PRINCIPAL'[MOTIVO]
      VAR PoliticaAtual = 'f_PRINCIPAL'[POLÍTICA]

      -- First step: Count the days a person was in the office in the current week
      VAR DiasNoEscritorio =
          CALCULATE(
              COUNTROWS('f_PRINCIPAL'),
              FILTER(
                  'f_PRINCIPAL',
                  'f_PRINCIPAL'[SEMANA] = SemanaAtual &&
                  'f_PRINCIPAL'[SIGLA] = SiglaAtual &&
                  'f_PRINCIPAL'[CONTADOR] = 1 &&
                  'f_PRINCIPAL'[MOTIVO] = "Presença"
              )
          )

      -- Second step: Calculate the possible days of going to the office
      VAR DiasSemana = 5
      VAR DiasAusentes =
          CALCULATE(
              COUNTROWS('f_PRINCIPAL'),
              FILTER(
                  'f_PRINCIPAL',
                  'f_PRINCIPAL'[SEMANA] = SemanaAtual &&
                  'f_PRINCIPAL'[SIGLA] = SiglaAtual &&
                  'f_PRINCIPAL'[MOTIVO] IN {"Férias", "Afastamento", "Ausência"}
              )
          )
      VAR DiasPossiveis = DiasSemana - DiasAusentes

      -- Third step: Calculate adherence (days of presence / possible days of going to the office)
      VAR Aderencia = DIVIDE(DiasNoEscritorio, DiasPossiveis, 0)

      -- Steps Four and Five: Verify that the Policy has been Followed
      RETURN
      IF(
          DiasPossiveis = 0,
          "Cumprida",
          SWITCH(
              TRUE(),
              PoliticaAtual = "3" && Aderencia > 0.6, "Cumprida",
              PoliticaAtual = "4" && Aderencia > 0.8, "Cumprida",
              "Não Cumprida"
          )
      )

      but the percentages still to be very low for all levels, it's show me the same numbers
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi amaral_diego ,

     

    You can try the following code.

    StatusAderencia =
VAR SemanaAtual = 'f_PRINCIPAL'[SEMANA]
VAR SiglaAtual = 'f_PRINCIPAL'[SIGLA]
VAR PoliticaAtual = 'f_PRINCIPAL'[POLÍTICA]
-- First step: Count the days a person was in the office in the current week
VAR DiasNoEscritorio =
    CALCULATE(
        COUNTROWS('f_PRINCIPAL'),
        FILTER(
            'f_PRINCIPAL',
            'f_PRINCIPAL'[SEMANA] = SemanaAtual &&
            'f_PRINCIPAL'[SIGLA] = SiglaAtual &&
            'f_PRINCIPAL'[CONTADOR] = 1 &&
            'f_PRINCIPAL'[MOTIVO] = "Presença"
        )
    )


    Make sure that the “CONTADOR” and “MOTIVO” columns are filtered correctly, and make sure that the values in the “POLÍTICA” column are correctly interpreted as “3” or “4” .

     

    If that didn't solve your problem. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

     

     

    Best Regards,

    Clara Gong

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