cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luisvieira95 Frequent Visitor
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

 

PROJECTACTIVITYSTATUS
P1A1ENCERRADO
P1A2PENDENTE
P1A3PENDENTE
P1A4PENDENTE
P1A5PENDENTE
P2A1ENCERRADO
P2A2ENCERRADO
P2A3PENDENTE
P2A4PENDENTE
P2A5PENDENTE
P3A1ENCERRADO
P3A2ENCERRADO
P3A3ENCERRADO
P3A4PENDENTE
P3A5PENDENTE
P4A1ENCERRADO
P4A2ENCERRADO
P4A3PENDENTE
P4A4PENDENTE
P4A5PENDENTE

 

 

 

CORRECT ANSWER:

 

 

P1A1ENCERRADOJUN 
P1A2PENDENTEA2PENDENTE 
P1A3PENDENTEA3PENDENTE 
P1A4PENDENTEA4PENDENTE 
P1A5PENDENTEA5PENDENTE 
P2A1ENCERRADOA1ENCERRADO 
P2A2ENCERRADOA2ENCERRADOTRUE
P2A3PENDENTEA3PENDENTETRUE
P2A4PENDENTEA4PENDENTE 
P2A5PENDENTEA5PENDENTE 
P3A1ENCERRADOA1ENCERRADO 
P3A2ENCERRADOA2ENCERRADO 
P3A3ENCERRADOA3ENCERRADO 
P3A4PENDENTEA4PENDENTE 
P3A5PENDENTEA5PENDENTE 
P4A1ENCERRADOA1ENCERRADO 
P4A2ENCERRADOA2ENCERRADOTRUE
P4A3PENDENTEA3PENDENTETRUE
P4A4PENDENTEA4PENDENTE 
P4A5PENDENTEA5PENDENTE 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
luisvieira95 Frequent Visitor
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()))

 

View solution in original post

6 REPLIES 6
ibarrau Senior Member
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 

 

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

themistoklis New Contributor
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()
)

 

Highlighted
ibarrau Senior Member
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 ()
)
luisvieira95 Frequent Visitor
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"

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

 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 201 members 2,521 guests
Please welcome our newest community members: