Forum Discussion

jderekc's avatar
jderekc
Helper IV
8 years ago
Solved

DAX - Variant Data Type error IF statement

Hi all,   I am writing a DAX formula to return a text result ("NED", which means "not enough data") if a quantity on hand of an item is less than a certain amount.  However, if it is true, then it ...
Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    try this 

     

    IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,NOT ISBLANK(InvAnalytics[Calc4_qOH])
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
) 

     

    this could be an alternative

     

    IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,InvAnalytics[Calc4_qOH] <> ""
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
)

     

    BLANK() returns a BLANK() meaning NULL value whereas ISBLANK(...) checks if a column reference is empty

     

    Regards

    Tom

TomMartens's avatar
TomMartens
Super User
8 years ago

Hey, you can't use in DAX, but you can use a DAX statement, that explicitly converts the numeric value to text, like so

IF(
	'Table1'[Column1] >= 2, FORMAT('Table1'[Column1],"#"), "too few")

Hope this helps

 

Regards

Tom

  • jderekc's avatar
    jderekc
    Helper IV
    8 years ago

    Hey Tom, that helps a lot, thanks!  I am having problems dealing with blank values/NULLs.  I tried to use the following but anything that comes across as blank will not show "NED" (in your case "too few") and instead shows "(Blank)":

     

    EstQtyOH = IF(InvAnalytics[Calc4_qOH] >= 2 && NOT BLANK(), FORMAT(InvAnalytics[Calc4_qOH], "#.##"), "NED") 

     

    • TomMartens's avatar
      TomMartens
      Super User
      8 years ago

      Hey,

       

      try this 

       

      IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,NOT ISBLANK(InvAnalytics[Calc4_qOH])
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
) 

       

      this could be an alternative

       

      IF(
  AND(
    InvAnalytics[Calc4_qOH] >= 2 
    ,InvAnalytics[Calc4_qOH] <> ""
  )
  ,FORMAT(InvAnalytics[Calc4_qOH], "#.##")
  , "NED"
)

       

      BLANK() returns a BLANK() meaning NULL value whereas ISBLANK(...) checks if a column reference is empty

       

      Regards

      Tom

      • jderekc's avatar
        jderekc
        Helper IV
        8 years ago

        Thanks for the prompt reply.  Sadly the issue persists with showing up as "(Blank)".  It doesn't make sense to me as it should have worked from the beginning.  I CAN attest to the fact it works if I increase the value to compare against (e.g.: make "2" a "500" and all values lower than "500" show up as "NED"), but it just doesn't want to work with blanks.