Forum Discussion
amaral_diego
Helper II
Helper IIOffice 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_diegoHelper II
Helper III'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_diegoHelper II
Helper IIHe 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
but the percentages still to be very low for all levels, it's show me the same numbers