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

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

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 392 members 3,967 guests
Recent signins: