Solved: Re: IF Formula Support

erhan_79 · ‎12-18-2021

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 :

AlexisOlson · ‎12-18-2021

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

View solution in original post

parry2k · ‎12-18-2021

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

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

AlexisOlson · ‎12-18-2021

Thank you. I will correct it.

erhan_79 · ‎12-18-2021

@AlexisOlson we need to change something in the formula ?

AlexisOlson · ‎12-18-2021

No, I already corrected it.

AlexisOlson · ‎12-18-2021

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