Conditional Logic - help

[tstraker]

Hi,

I need some help identifying rows with conditional logic in poweBI Desktop, I've outlined the details below.

1. I have a risk table in my project. [RiskCombined]
2. I want to report on all Open, Active or New risks
3. I want to then filter to all risks with an impact and likelihood rating of 4 and above
4. I also want to highlight any risks with a tag of "portfolio" irrespective of their impact and likelihood rating in step 3 (an escalation tag).

I've attempted to create a new custom column within the query editor with the following conditions:

repLogic = 
if (
    [Status] = "Open" or "Active" or "New"
    and
    ([Impact] >= 4 and [Likelihood] >= 4
    or
    Text.Contains([TagNames], "portfolio")
    ))
then 1
else 0

The Idea is that I would visualize all risks with a replogic value of 1

I don't get any syntax errors but its clearly not working how I intended it to 😉

I get the following error in most but not all rows:

Expression.Error: We cannot convert the value "Active" to type Logical.
Details:
    Value=Active
    Type=Type

I understand that I'm mixing boolean with text so I'll need to find a way to convert the [Status] value to a boolean.

My questions:

1.  What's the best/quickest way to get a true/false value that I can use to filter risks (based on the criteria above)
2.  Should I be using a measure or a series of measures instead of a cal column?
3.  Should I be using VARS in this case?
4. Can anyone provide me with an example?

[parry2k · Accepted solution]

@tstraker try following code

if (
   ( [Status] = "Open" or [Status] = "Active" or [Status] = "New")
    and
(
    ([Impact] >= 4 and [Likelihood] >= 4)
    or
    Text.Contains([TagNames], "portfolio")
   )
then 1
else 0

[tstraker]

Thanks @parry2k 

I was prompted for an additional right parenthesis, I added that and ran it (screenshot below).

I then had to clean up a few null values in my [TagNames] and [Status] fields but it worked!
Thanks for the tweak!