Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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
 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

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

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

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

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

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

     

    -FILTER(ALLSELECTED(NGPricesOneLine)

     

    -->

     

    ,FILTER(ALLSELECTED(NGPricesOneLine)

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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