IF Formula Support

[erhan_79]

Hi There ;

i need you kind support 

i have 4 columns as below , "Order Value " , Invoice Value" , "Order No", Invice No" named

İ want to create a new column as named "Statu" which one marked with yellow .New column's rules will be like that 

• When  Order Value and Invoice Value is not blank  : 
  • Check Order no and Invoice no , if one of Order no or Invoice no is blank statu will be "opened " , if both of them is not blank statu will be " closed"
• When  Order Value is not blank  but Invoice Value is blank  :
  • Check only Order no, if  Order no is blank statu will be "opened " , if  it is not blank   statu will be " closed"
• When  Order Value is blank  but Invoice Value is not blank  :
  • Check only Invoice no, if  Invoice no is blank statu will be "opened " , if  it is not blank   statu will be " closed"

You can see an example table as below i wat to create like that : 

[parry2k]

@AlexisOlson one correction:

VAR NoI = ISBLANK ( Table1[Invoice No] ) //instead of order number it will be invoice number

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[AlexisOlson]

Thank you. I will correct it.

[erhan_79]

@AlexisOlson  we need to change something in the formula  ? 

[AlexisOlson]

No, I already corrected it.

[AlexisOlson · Accepted solution]

I'd recommend using SWITCH instead of IF.

Statu =
VAR ValO = ISBLANK ( Table1[Order Value] )
VAR ValI = ISBLANK ( Table1[Invoice Value] )
VAR NoO = ISBLANK ( Table1[Order No] )
VAR NoI = ISBLANK ( Table1[Invoice No] )
RETURN
    SWITCH (
        TRUE (),
        NOT ValO && NOT ValI && (   NoO ||     NoI ), "Opened",
        NOT ValO && NOT ValI && NOT NoO && NOT NoI,   "Closed",
        NOT ValO &&     ValI &&     NoO,              "Opened",
        NOT ValO &&     ValI && NOT NoO,              "Closed",
            ValO && NOT ValI &&     NoI,              "Opened",
            ValO && NOT ValI && NOT NoI,              "Closed"
    )