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 "...
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