topic DAX: Conditional in Calculated Column in DAX Commands and Tips

DAX: Conditional in Calculated Column

amaral_diego — Wed, 30 Oct 2024 18:30:20 GMT

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:

I have a column called “MOTIVO” with values like “Afastamento”, “Ausência”, “Falta”, “Férias”, and “Presença”.
I have another column called “CONTADOR” with values “0” and “1”.
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!

Re: DAX: Conditional in Calculated Column

Anonymous — Thu, 31 Oct 2024 01:44:44 GMT

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.

Re: DAX: Conditional in Calculated Column

amaral_diego — Thu, 31 Oct 2024 12:15:47 GMT

It's worked, thanks a lot!