cancel
Showing results 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.

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
Super User

Hi @BishwaR

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!

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.
7 REPLIES 7
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.
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!

Helper V

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

Super User

Hi @BishwaR

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!

Helper V

Thank you

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
Helper V

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors