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

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.

    • TomMartens's avatar
      TomMartens
      Super User
      8 years ago

      Hey, can you share some sample data, by sharing a link that points to the file on onedrive or dropbox.

       

      Is there a relationship to the calculated column?

      "(Blank)" shows up, when a column is used on a visual and this column is on the one-side of a relationship and the corresponding column on the many-side contains more values (meaning other values) than the column on the one-side.

       

      Regards

      Tom

      • jderekc's avatar
        jderekc
        Helper IV
        8 years ago

        Thanks, Tom!  I unfortunately cannot share the data as it is sensitive to my company.  Not that anyone on here would care, but just know that I am unable to share this particular set of data (contains sales, margins, inventory, vendors, et cetera).  It's okay if we can't figure it out as I'll keep plugging away.  :)

         

        As far as the relationships:  this calculated column is from a table I created via DAX using NATURALINNERJOINs.  I had three separate SQL queries that were tough--and not readily apparent how--to combine into one, so I used that.  The relationship for the table is a one-to-one relationship between the "Items" master table and the "InvAnalytics" table.  The cross filter direction is set to "both".  The relationship is active.

         

        Here's a breakdown of some of my tables:

         

        Table: Cases_PYN30Days

        Columns: PYNItem, CasesPYN30

         

        1-to-1 relationship with Cases_CY90PDays

         

        Table: Cases_PY90PDays

        Columns: PYItem, CasesPYP90

         

        1-to-1 relationship with Cases_CY90PDays

        Many-to-one relationship with StockOH

        Many-to-one relationship with StockOHRep

        Many-to-one relationship with Specials

         

        Table: Cases_CY90PDays

        Columns: CYItem, CasesCYP90

         

        1-to-1 relationship with Cases_PY90PDays

        1-to-1 relationship with Cases_PYN30Days

         

        Table: Cases_OnHand

        Columns: OHItem, QTYONORD, QTYONHND

         

        1-to-1 relationship with Cases_PYN30Days

         

        Table: InvAnalytics

        Columns: PYItem, CasesPYP90, CYItem, CasesCYP90, PYNItem, CasesPYN30, Calc1_PCT, Calc2nPY, Calc3_pDay, OHItem, QTYONORD, QTYONHND, Calc4_qOH, EstQTYOH

         

        1-to-1 relationship with Items table

         

         

        Best regards,

        Derek

         

    • 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