Forum Discussion
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
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 example1>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
RegardsPhil
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
7 Replies
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.
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
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 example1>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
RegardsPhil
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
