IF STATEMENT

[SelflearningBi]

Model Name	OD1	OD2	OD3	OD4	OD5
A	YES	YES			YES
B	YES	NO	NO
C	NO	YES
D		YES
E	YES	YES	YES

How to define in each row has only Yes then " OK" else "Remove" 
In this case: A ,D, and E are "ok" else are "remove" 

In my actual database, I have lots of OD.. I would like to have a easier way to define this logic.
How to write a measure or column with this logic? 

[jcalheir]

Hi

Create a calculeted column that tests if any column has the value "NO":

IF(
    AND(
        'table'[OD1] <> "NO",
        'table'[OD2] <> "NO",
        'table'[OD3] <> "NO",
        ),
    "OK",
    "REMOVE"
)

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[JTBusinessIntel · Accepted solution]

The AND function only accepts two arguments, so you'd need to nest a second AND statement as your second argument or replace the AND function with "&&" for comparison.

If you're comparing 5 fields, you would need to do a series of nested AND statements prior to returning your IF statement.
 

IF(
    AND(
        'table'[OD1] <> "NO",
    AND('table'[OD2] <> "NO",
    AND('table'[OD3] <> "NO",
    AND('table'[OD4] <> "NO",
        'table'[OD5] <> "NO"
        )))),
    "OK",
    "REMOVE"
)

[SelflearningBi]

Hi, how to include the blank condition?  like none of NO and all blank() - without defineig YES/NO

[JTBusinessIntel]

There are a couple of options to tackle that.

I would set it up for each argument in the AND statement, to include an OR statement. 

IF(
    AND(
        OR('table'[OD1] <> "NO",'table'[OD1] <> Blank()),
    AND(
        OR('table'[OD2] <> "NO",'table'[OD2] <> Blank()),
    AND(
        OR('table'[OD3] <> "NO",'table'[OD3] <> Blank()),
    AND(
        OR('table'[OD4] <> "NO",'table'[OD4] <> Blank()),
        OR('table'[OD5] <> "NO",'table'[OD5] <> Blank())
        )))),
    "OK",
    "REMOVE"
)

Setting it up this way means each of the AND statements is evaluating that  the value in that column does not equal "NO" or "Blank()". If it contains either of those values it will return "REMOVE". If there is a valid Text within the table, it will return "OK".

Hopefully that works for you!

[SelflearningBi]

alright, I see the trick, its this saying each row has one not equal to "NO" then "OK"? 

[jcalheir]

Yes, if none are "NO" the returns OK

[SelflearningBi]

HOW DID YOU ADD 3 CONDITIONS WITH AND?