cancel
Showing results for
Did you mean:
Frequent Visitor

## Error with Nested IF and Calculate/Filter Condition

Hi

I am trying to calculate a column called Differential based on different IF condition . However , when I am using nested IF statements I am getting this error. I tried using Switch statement too but that didnt work because Switch doesnt work with calculated columns ?

Query

NGFinal = SUMMARIZE('NGPricesOneLine','NGPricesOneLine'[Source Name],'NGPricesOneLine'[Month],'NGPricesOneLine'[PriorSettle],"Differential",If(
'NGPricesOneLine'[Source Name] = "ABC",
SUM('NGPricesOneLine'[PriorSettle]),
If(     'NGPricesOneLine'[Source Name]IN {"C","D","E","F"},
CALCULATE(SUM(NGPricesOneLine[PriorSettle]), CALCULATE(SUM('NGPricesOneLine'[PriorSettle])) -FILTER(ALLSELECTED(NGPricesOneLine), NGPricesOneLine[Month] =MIN('NGPricesOneLine'[Month]) && 'NGPricesOneLine'[Source Name]="ABC")),
If(     'NGPricesOneLine'[Source Name]IN {"G","H","I"},
CALCULATE(SUM('NGPricesOneLine'[PriorSettle]), CALCULATE(SUM(NGPricesOneLine[PriorSettle]), FILTER(ALLSELECTED(NGPricesOneLine), NGPricesOneLine[Month] =MIN(NGPricesOneLine[Month]) && 'NGPricesOneLine'[Source Name]="ABC")) -CALCULATE(SUM('NGPricesOneLine'[PriorSettle]))

))
)))

Error : A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

The same query works if I am using only One IF statement .

Would appreciate if someone could guide , what am I doing wrong

Thanks

1 ACCEPTED SOLUTION
Frequent Visitor

Re-wrote the query and it worked. Thanks all for your inputs

5 REPLIES 5
Community Support

Hi @prisharm ,

I found a negative sign before FILTER(). I think it may be a comma.

``-FILTER(ALLSELECTED(NGPricesOneLine)``

-->

``,FILTER(ALLSELECTED(NGPricesOneLine)``

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

Thanks for your response. "-" sign is for subtraction. I have to subtract corresponding values for different sources from ABC or ice versa. Have explained the issue above with sample source data .

Thanks for your help

Super User III

Here's some general guidance: Look at your query on http://www.daxformatter.com/

That helps you visualize the logic, and also helps you identify repetitive calculations that are better done once, using variables.

Also note that "ABC" is not the same as {"A","B","C"} but I guess you knew that already.

Frequent Visitor

Thanks for the reply lbendlin I ran my query through DAX formatter and it looks okay. Couldnt find any flaws .

I didnt copy my source data for you all to understand and suggest changes . Mentioned below is my source data

Differential is a calculated column

 Differential Source Name Month PriorSettle 1 ABC Jul-20 1 2 ABC Aug-20 2 3 ABC Sep-20 3 4-2=2 C Aug-20 4 1-3=-2 C Sep-20 1 2-0=2 C Oct-20 2 2-1=1 D Jul-20 2 5-2=3 D Aug-20 5 1-4=-3 G Jul-20 4 2-2=0 G Aug-20 2

I have to calculate this column on the basis of where Source is "ABC" and a matching month , if Source is C,D, .. , I should subtract the value of "ABC" from C,D.. if its G, H.. I have to subtract the values from "ABC". IF its ABC , the priorsettle should stay as is. PLease note all of this needs to be matched on a month.

I am pretty new to POwerBI hence please feel free to suggest if my entire apprach to calculate these values is not correct

Thanks for your help

Frequent Visitor

Re-wrote the query and it worked. Thanks all for your inputs

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!