Error with Nested IF and Calculate/Filter Condition

[Anonymous]

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

[v-eachen-msft]

Hi @Anonymous ,

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

-FILTER(ALLSELECTED(NGPricesOneLine)

-->

,FILTER(ALLSELECTED(NGPricesOneLine)

[Anonymous]

Hi v-eachen-msft

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

[lbendlin]

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.

[Anonymous]

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

[Anonymous · Accepted solution]

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