Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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"
)
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! 🙂
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"
)
Hi, how to include the blank condition? like none of NO and all blank() - without defineig YES/NO
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!
alright, I see the trick, its this saying each row has one not equal to "NO" then "OK"?
Yes, if none are "NO" the returns OK
HOW DID YOU ADD 3 CONDITIONS WITH AND?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |