cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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

View solution in original post

5 REPLIES 5
Community Support
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.

Hi 

 

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

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 

 

DifferentialSource NameMonth PriorSettle
1ABCJul-201
2ABCAug-202
3ABCSep-203
4-2=2CAug-204
1-3=-2CSep-201
2-0=2COct-202
2-1=1DJul-202
5-2=3DAug-205
1-4=-3GJul-204
2-2=0GAug-202

 

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

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

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

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

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors