Custom column with multiple If statements for picking row below

[luisvieira95]

Hi!

I'm developing a DAX formula to show the projects that obey the rule below:

IF (ACTIVITY = A2  AND STATUS= ENCERRADO) AND (ACTIVITY = A3 AND STATUS= PENDENTE):
COLUMN FORMULA = "TRUE"

ELSE:

BLANK

PROJECT	ACTIVITY	STATUS
P1	A1	ENCERRADO
P1	A2	PENDENTE
P1	A3	PENDENTE
P1	A4	PENDENTE
P1	A5	PENDENTE
P2	A1	ENCERRADO
P2	A2	ENCERRADO
P2	A3	PENDENTE
P2	A4	PENDENTE
P2	A5	PENDENTE
P3	A1	ENCERRADO
P3	A2	ENCERRADO
P3	A3	ENCERRADO
P3	A4	PENDENTE
P3	A5	PENDENTE
P4	A1	ENCERRADO
P4	A2	ENCERRADO
P4	A3	PENDENTE
P4	A4	PENDENTE
P4	A5	PENDENTE

CORRECT ANSWER:

P1	A1	ENCERRADO	JUN
P1	A2	PENDENTE	A2PENDENTE
P1	A3	PENDENTE	A3PENDENTE
P1	A4	PENDENTE	A4PENDENTE
P1	A5	PENDENTE	A5PENDENTE
P2	A1	ENCERRADO	A1ENCERRADO
P2	A2	ENCERRADO	A2ENCERRADO	TRUE
P2	A3	PENDENTE	A3PENDENTE	TRUE
P2	A4	PENDENTE	A4PENDENTE
P2	A5	PENDENTE	A5PENDENTE
P3	A1	ENCERRADO	A1ENCERRADO
P3	A2	ENCERRADO	A2ENCERRADO
P3	A3	ENCERRADO	A3ENCERRADO
P3	A4	PENDENTE	A4PENDENTE
P3	A5	PENDENTE	A5PENDENTE
P4	A1	ENCERRADO	A1ENCERRADO
P4	A2	ENCERRADO	A2ENCERRADO	TRUE
P4	A3	PENDENTE	A3PENDENTE	TRUE
P4	A4	PENDENTE	A4PENDENTE
P4	A5	PENDENTE	A5PENDENTE

How can my dax formula pick up the valeu below the first if statement to show the correct project?

I have made this formula concatenating the  ACTIVITY and STATUS columns, but it doesnt show all the projects because its missing a IF statement

 COND = IF(teste[JUN] = "A2ENCERRADO";"TRUE";BLANK())

[themistoklis]

@ibarrau

You can also use the switch function:

Column =
SWITCH (
    TRUE ();
    'Table'[Activity] = 'A2' && 'Table'[Status] = 'ENCERRADO'; 'TRUE';
    'Table'[Activity] = 'A3' && 'Table'[Status] = 'PENDENTE'; 'TRUE';
    BLANK()
)

[ibarrau]

Hi, you can try && instead of AND in your formula or can try "AND" dax function: 

= IF (
    AND (
        AND ( ACTIVITY = A2, STATUS = ENCERRADO ),
        AND ( ACTIVITY = A3, STATUS = PENDENTE )
    ),
    TRUE (),
    BLANK ()
)

Let me know if it works 

[luisvieira95]

Hello, thank you for your support, but with this formula every result in the custom column was "False"

CODIGO = IF (
    AND (
        AND ( teste[ACTIVITY] = "A2"; teste[STATUS] = "ENCERRADO" );
        AND ( teste[ACTIVITY] = "A3"; teste[STATUS] = "PENDENTE" )
    );
    TRUE ();
    BLANK ()
)

[ibarrau]

Are you sure you have to ask with AND? this means all the columns should respond to that condition in order to show true(). Maybe you need an "OR" in some place.

 I think you are looking for this:

= IF (
    OR (
        AND ( ACTIVITY = A2, STATUS = ENCERRADO ),
        AND ( ACTIVITY = A3, STATUS = PENDENTE )
    ),
    TRUE (),
    BLANK ()
)

[luisvieira95]

I tried to use the conditionals but they did not work.
Only if A2 and A3 are in the sequence reported in the correct answer that the formula must be correct as well.

IF A2 = ENCERRADO and next line A3 = PENDENTE:
"TRUE"
ELSE:
"FALSE"

[luisvieira95 · Accepted solution]

I found a solution

First I made a calculated column

Column = IF (
    OR (
        AND ( teste[ACTIVITY] = "A2"; teste[ESTATUS] = "ENCERRADO" );   
        AND ( teste[ACTIVITY] = "A3"; teste[ESTATUS] = "PENDENTE" )
    );
    TRUE ();
    FALSE()
)

And than a measure with a slicer to get only true subjects in a sequence

Column 3 = COUNTROWS(filter(teste;teste[Column]=TRUE()))