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

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.

v-chuncz-msft's avatar
v-chuncz-msft
Community Support
8 years ago

jderekc,

 

You may illustrate with some dummy data or post some screenshots.

  • jderekc's avatar
    jderekc
    Helper IV
    8 years ago

    I can try to do that.  There's quite a bit of data, though, so I'm unsure the best way of presenting a lot of dummy data, so selective screenshots may be useful.  What data in particular needs to be looked at?  Table relationships?  I did mention that in my previous post.  Let me know.  Thanks!

     

    - Derek

    • TomMartens's avatar
      TomMartens
      Super User
      8 years ago

      Hey,

       

      posting screenshot is useful to some extent, but I personally like data.

      Posting scrrenshots, puts a lot of burden to the people who like me also need data, to better understand your requirement, by typing the data. For this reason I appreciate if you would use the "Enter Data" funtion to create a simple tabel model, enter data manually or copy some data columnwise from excel, also create the relationships.

       

      You entered enough data when the issue is reproducable :-)

       

      Upload the Pbix file to onedrive or dropbox (for some policy reason, personally I just can use these both) and share the link here.

       

      Regards

      Tom

    • jderekc's avatar
      jderekc
      Helper IV
      8 years ago

      I believe the problem with the "BLANK"s is that sometimes I am dividing by NULL data (no data available).  I've tried throwing in IFERROR and ISBLANK but can't seem to convert any divide-by-null operations into plain zeroes.  Is this possible?  Thanks!

       

      - Derek