Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
BishwaR
Helper V
Helper V

Simplify the code

I have DAX like Days Remaining = CALCULATE (IF (AND ( ISNUMBER ( SUM ( COST AMOUNT)),

                                                             AND ( SUM(COST AMOUNT) <>0,

                                                             AND SUM( INVENTORY) <>0 )

                                                            ), 365/ ( ( SUM (COST AMOUT) *4)/ SUM(INVENTORY) ), " "),
                                                            FILTER ( DATE = TODAY())

 

How does IF(AND work ?

I understood the other part but IF(AND confused me.

If you could give me a simple example to understand the concept I would be grateful. Thanks

                                    

2 ACCEPTED SOLUTIONS

Hi @BishwaR 

 

OK well let's start with IF.

 

IF tests a condition and then gives a result if that condition is true, and a different result if that condition is false.

 

Syntax: IF(Condition, Result_If_True, Result_If_False)


Example: IF(1>0, "Positive", "Negative")

 

The result is "Positive"


Example: IF(-1>0, "Positive", "Negative")

 

The result is "Negative"


Now let's look at AND. The AND function tests if all arguments are TRUE and returns TRUE if they are. Otherwise it returns FALSE.

 

Syntax: AND(Argument_1, Argument_2)


Example: AND(1>0, 2>0)

 

Result: TRUE

 

 

Example: AND(-1>0, 2>0)

 

Result: FALSE

 


Example: AND(-1>0, -2>0)

 

Result: FALSE

 


Example: AND(1>0, -2>0)

 

Result: FALSE


NOTE: The AND function only accepts 2 arguments. If you need to test 3 or more arguments/expressions then use && for example

 

1>0 && 2>0 && 3>0

 

Result: TRUE


So getting back to using IF with AND, I'll use the examples from above. Remember that the syntax for IF is IF(Condition, Result_If_True, Result_If_False)


Example: IF(AND(1>0, 2>0), "Positive", "Negative")

 

Result : "Positive" because AND(1>0, 2>0) returns TRUE. Therefore IF returns the Result_If_True


Example: IF(AND(-1>0, 2>0), "Positive", "Negative")

 

Result : "Negative" because AND(-1>0, 2>0) returns FALSE. Therefore IF returns the Result_If_False


Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

v-jayw-msft
Community Support
Community Support

Hi @BishwaR ,

 

The first AND() in your formula is used to wrap the following parallel conditions, and has nothing to do with IF() function.

Days Remaining = CALCULATE (IF (AND ( ISNUMBER ( SUM ( COST AMOUNT)),

                                                             AND ( SUM(COST AMOUNT) <>0,

                                                             AND SUM( INVENTORY) <>0 )

                                                            ), 365/ ( ( SUM (COST AMOUT) *4)/ SUM(INVENTORY) ), " "),
                                                            FILTER ( DATE = TODAY())

The problem should be the third AND() in your formula. There are no parentheses after it and it seems redundant. Please check if your formula is correct.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

7 REPLIES 7
v-jayw-msft
Community Support
Community Support

Hi @BishwaR ,

 

The first AND() in your formula is used to wrap the following parallel conditions, and has nothing to do with IF() function.

Days Remaining = CALCULATE (IF (AND ( ISNUMBER ( SUM ( COST AMOUNT)),

                                                             AND ( SUM(COST AMOUNT) <>0,

                                                             AND SUM( INVENTORY) <>0 )

                                                            ), 365/ ( ( SUM (COST AMOUT) *4)/ SUM(INVENTORY) ), " "),
                                                            FILTER ( DATE = TODAY())

The problem should be the third AND() in your formula. There are no parentheses after it and it seems redundant. Please check if your formula is correct.

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
PhilipTreacy
Super User
Super User

Hi @BishwaR 

 

Here's some doco on IF and AND

 

IF – DAX Guide

 

AND – DAX Guide

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thanks for the info. Could you please give me and example off writing IF condition using IF(AND( TRUE,FLASE) ? I am trying to understand the syntex. I know how to do it using && but some developers uses IF(AND (--

 

Thanks

Hi @BishwaR 

 

OK well let's start with IF.

 

IF tests a condition and then gives a result if that condition is true, and a different result if that condition is false.

 

Syntax: IF(Condition, Result_If_True, Result_If_False)


Example: IF(1>0, "Positive", "Negative")

 

The result is "Positive"


Example: IF(-1>0, "Positive", "Negative")

 

The result is "Negative"


Now let's look at AND. The AND function tests if all arguments are TRUE and returns TRUE if they are. Otherwise it returns FALSE.

 

Syntax: AND(Argument_1, Argument_2)


Example: AND(1>0, 2>0)

 

Result: TRUE

 

 

Example: AND(-1>0, 2>0)

 

Result: FALSE

 


Example: AND(-1>0, -2>0)

 

Result: FALSE

 


Example: AND(1>0, -2>0)

 

Result: FALSE


NOTE: The AND function only accepts 2 arguments. If you need to test 3 or more arguments/expressions then use && for example

 

1>0 && 2>0 && 3>0

 

Result: TRUE


So getting back to using IF with AND, I'll use the examples from above. Remember that the syntax for IF is IF(Condition, Result_If_True, Result_If_False)


Example: IF(AND(1>0, 2>0), "Positive", "Negative")

 

Result : "Positive" because AND(1>0, 2>0) returns TRUE. Therefore IF returns the Result_If_True


Example: IF(AND(-1>0, 2>0), "Positive", "Negative")

 

Result : "Negative" because AND(-1>0, 2>0) returns FALSE. Therefore IF returns the Result_If_False


Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thank you

Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar table with a relationship from the Date column of your Data Table to the Date column of your Calendar table.  To your slicer, drag the Date column from the Calendar Table and select today's date.  Write these measures

Cost = sum(Data[Cost amount])

Inv = sum(Data[Inventory])

Measure1 = calculate(divide(365,divide([cost]*4,[inv])),filter(data,[cost]>0&&[inv]<>0))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you. But I was trying to understand the concept of IF(AND. I would appreciate if you could explain how does this expression work 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.