Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
2 months ago
Solved

Nested IF

Hi to all can anyone help me on my calculated column. Basically im not sure how to explain it well but the formula in excel is like this RESULT=IF(A2="Article",IF(C2<0,IF(B2>0,1,0),0),IFERROR(D2...
Need Help
  • MFelix's avatar
    MFelix
    2 months ago

    Hi AllanBerces ,

     

    If you need to have it on a calculated column and the errors are are blank values then just use this formula:

     

    Result =
         IF('Table'[ART/OBJ] = "Article" ,
		 IF ('Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 ,
			IF( 'Table'[OBJECTREQUIRED] > 0 , 1, 0), 0), 
				IF(ISBLANK('Table'[O/S AS PER STATUS]),1, 'Table'[O/S AS PER STATUS])
)

     

    Or

    Result =
SWITCH(TRUE(),
    'Table'[ART/OBJ] = "Article" && 'Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 && 'Table'[OBJECTREQUIRED] > 0 ,1,
      ISBLANK('Table'[O/S AS PER STATUS]), 1,
           'Table'[O/S AS PER STATUS]
	)

     

     

MFelix's avatar
MFelix
Super User
2 months ago

Hi AllanBerces ,

 

If you are getting this information from the Excel file be carefull because the #N/A will not load the data properly and you will get an error on the cells like below:

 

This will make your power query not to load all rows so you also need to handle the errors, however in your formula you are also not considering if there is an error on cell C3 so this can mess up your calculation because if you have an article with C column error then you will get an error on the final colum try the following code on Power Query:

try if [#"ART/OBJ"] = "Article" then 
      
      if [#"STOCK DEV. (AGAINST RCVD STOCK)"] <0 then 
        1 else       
        
        if [OBJECTREQUIRED] > 0 then 1 else 0


         else [#"O/S AS PER STATUS"]

         otherwise 1

 

You should also handle those errors doing the replacment of values:

 Table.ReplaceErrorValues(#"Personalizado Adicionado", {{"O/S AS PER STATUS", 0}})

Where there is Zero just place whatever value you think is correct.

 

 

  • AllanBerces's avatar
    AllanBerces
    Post Prodigy
    2 months ago

    Hi MFelix thank you for the reply, but can i have it in calculated column.i'm doing it on the calculated column. The #N/A shows blank on my calculated column

    • MFelix's avatar
      MFelix
      Super User
      2 months ago

      Hi AllanBerces ,

       

      If you need to have it on a calculated column and the errors are are blank values then just use this formula:

       

      Result =
         IF('Table'[ART/OBJ] = "Article" ,
		 IF ('Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 ,
			IF( 'Table'[OBJECTREQUIRED] > 0 , 1, 0), 0), 
				IF(ISBLANK('Table'[O/S AS PER STATUS]),1, 'Table'[O/S AS PER STATUS])
)

       

      Or

      Result =
SWITCH(TRUE(),
    'Table'[ART/OBJ] = "Article" && 'Table'[STOCK DEV. (AGAINST RCVD STOCK)] < 0 && 'Table'[OBJECTREQUIRED] > 0 ,1,
      ISBLANK('Table'[O/S AS PER STATUS]), 1,
           'Table'[O/S AS PER STATUS]
	)