Struggling with an IF rule

[PowerBI-Curious]

Hi All 

I cannot get this to work, I feel like it should be easier then I am making it!

What I need to occur is: 

If non compliant> 1 and comments >1 Yes

If non compliant>1 and comments <1 No

If non compliant <1 N/A

https://1drv.ms/x/s!ArM9L8ohYGT4gQhszs0ssIc8haUT

Thank you!!

PowerBICurious

[Anonymous]

You have the best username ever, so... let's fix you up! 🙂

Are you trying to make a new calculated column based on those rules?

What do you want to happen if non-complaint = 1 ?

[PowerBI-Curious]

Hi @Anonymous thanks for your quick response - I was hoping the name might help me out

Yes I want a new calculated column based on those rules (unless there is a better option)

So if non-compliant = 1 that is when I want it to say yes or no to there being comments.

If there are no non-compliance there is no need for comments so that should be a straight N/A.

Does that make sense?

[v-ljerr-msft · Accepted solution]

Hi @PowerBI-Curious,

After looking into your shared sample data, I found the data type of  "Comments" column is Text type(with numbers and the actual comments).

Based on my test, the formula below should work in your scenario. 

Column = 
IF (
    'CCC Verification List Report'[Non-Compliant] >= 1,
    IF (
        VALUE ( LEFT ( 'CCC Verification List Report'[Comments], 1 ) ) >= 1,
        "Yes",
        "No"
    ),
    "N/A"
)

Regards

[PowerBI-Curious]

Thank you, this worked perfectly 🙂

[Anonymous]

So actually (adjustments in red):

If non compliant>=1 and comments >=1 Yes

If non compliant>=1 and comments <=1 No

If non compliant <1 N/A

Something like:

NewColumn = IF (MyTable[NonCompliant] >= 1,
    IF (MyTable[Comments] >= 1, "Yes", "No"),
    "N/A"
)