Forum Discussion

erhan_79's avatar
erhan_79
Post Prodigy
4 years ago
Solved

IF Formula Support

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 : 

 

 

 

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    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"
    )

     

     

5 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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"
    )

     

     

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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.⚡