Simplify the code

[BishwaR]

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

[PhilipTreacy · Accepted solution]

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

[v-jayw-msft · Accepted solution]

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

[PhilipTreacy]

Hi @BishwaR 

Here's some doco on IF and AND

IF – DAX Guide

AND – DAX Guide

Regards

Phil

[BishwaR]

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

[PhilipTreacy · Accepted solution]

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

[BishwaR]

Thank you

[Ashish_Mathur]

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.

[BishwaR]

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