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,1))

OUTPUT

ART/OBJOBJECTREQUIREDSTOCK DEV. (AGAINST RCVD STOCK)O/S AS PER STATUSRESULT
Article0.7-0.7#N/A1
Object0#N/A#N/A1
Object0#N/A#N/A1
Object0#N/A#N/A1
Object0#N/A#N/A1
Article18-18#N/A1
Object0#N/A#N/A1
Object0#N/A#N/A1
Article0000
Article0400
Article0000
Article11.6800
Object0#N/A00
Object0#N/A00
Object0#N/A00



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]
	)

     

     

5 Replies

  • 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]
	)

         

         

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    2 months ago

    Hi AllanBerces ,

    When you have a moment, please review the latest response from MFelix  and let us know if it meets your expectations. If you need any further information or clarification, please let us know.

    Thanks for your input MFelix .

     

    Regards,
    Yugandhar.