cancel
Showing results for
Did you mean:
Frequent Visitor

Custom column with multiple If statements for picking row below

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

Re: Custom column with multiple If statements for picking row below

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()))`

6 REPLIES 6
Established Member

Re: Custom column with multiple If statements for picking row below

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

Frequent Visitor

Re: Custom column with multiple If statements for picking row below

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 ()
)

New Contributor

Re: Custom column with multiple If statements for picking row below

@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()
)```

Established Member

Re: Custom column with multiple If statements for picking row below

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 ()
)```
Highlighted
Frequent Visitor

Re: Custom column with multiple If statements for picking row below

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"

Frequent Visitor

Re: Custom column with multiple If statements for picking row below

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()))`