DAX: Conditional in Calculated Column

[amaral_diego]

Hi everyone,

I’m working on a calculated column in Power BI and need some help with DAX. Here is what I’m trying to achieve:

1. I have a column called “MOTIVO” with values like “Afastamento”, “Ausência”, “Falta”, “Férias”, and “Presença”.
2. I have another column called “CONTADOR” with values “0” and “1”.
3. I need to create a calculated column that:
   • Shows “Cumpriu” if the value “1” appears 3 times or more in the “CONTADOR” column within the same week.
   • Shows “Não Cumpriu” if the value “1” appears less than 3 times in the “CONTADOR” column within the same week.
   • Shows “Afastamento” or “Férias” if the “MOTIVO” column has these values, regardless of the “CONTADOR” values.

Here is the DAX code I have so far:

StatusPolitica = 
VAR SemanaAtual = WEEKNUM('PRINCIPAL'[DIA], 2)
VAR SiglaAtual = 'PRINCIPAL'[SIGLA]
VAR MotivoAtual = SELECTEDVALUE('PRINCIPAL'[MOTIVO])
VAR MarcaçõesSemana = 
    CALCULATE(
        COUNTROWS('PRINCIPAL'),
        FILTER(
            'PRINCIPAL',
            WEEKNUM('PRINCIPAL'[DIA], 2) = SemanaAtual &&
            'PRINCIPAL'[SIGLA] = SiglaAtual &&
            'PRINCIPAL'[CONTADOR] = 1
        )
    )

RETURN
IF(
    MotivoAtual IN {"Afastamento", "Férias"},
    MotivoAtual,
    IF(
        MarcaçõesSemana >= 3,
        "Cumpriu",
        "Não Cumpriu"
    )
)

However, this code isn’t working as expected. It still shows “Cumpriu” or “Não Cumpriu” even when the “MOTIVO” column has “Afastamento” or “Férias”.

Could someone help me identify what I’m doing wrong or suggest a better approach?

Thank you!

---

Feel free to adjust the wording as needed. This should provide a clear and detailed explanation of your issue to the community. If you need any more help, let me know! 

[Anonymous · Accepted solution]

Hi @amaral_diego ,

You can try this calculation column.

StatusPolitica = 
VAR SemanaAtual = WEEKNUM('PRINCIPAL'[DIA], 2)
VAR SiglaAtual = 'PRINCIPAL'[SIGLA]
VAR MotivoAtual = 'PRINCIPAL'[MOTIVO]
VAR Marca = 
    CALCULATE(
        COUNTROWS('PRINCIPAL'),
        FILTER(
            'PRINCIPAL',
            WEEKNUM('PRINCIPAL'[DIA], 2) = SemanaAtual &&
            'PRINCIPAL'[SIGLA] = SiglaAtual &&
            'PRINCIPAL'[CONTADOR] = 1
        )
    )

RETURN
IF(
    MotivoAtual IN {"Afastamento", "Férias"},
    MotivoAtual,
    IF(
        Marca >= 3,
        "Cumpriu",
        "Não Cumpriu"
    )
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

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.

[amaral_diego]

It's worked, thanks a lot!