cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Frequent Visitor

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
Community Champion

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

Super User

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

Frequent Visitor

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

Super User

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

Frequent Visitor

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

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