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

It looks like it is because those darn items aren't even in the new table I created!!!!  So of course they are blank!  They do not exist in the table the calculations are in whatsoever.  Sorry about that.  I might need to change my natural inner joins to some outer joins via DAX.

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

jderekc,

 

Any progress, feel free to share with us.

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

    jderekc,

     

    A simplified example would help a lot.

  • jderekc's avatar
    jderekc
    Helper IV
    8 years ago

    Basically it comes down to products sold in a certain date range.  In SQL in my WHERE clause, I have the date ranges set that I want to query against.  Instead of tens of thousands of potential product matches, I am under ten thousand for a particular date range.  The items in my Power BI report include ALL products.  Thus, since a product isn't found in my query (not even a NULL value), then I get a blank return.  I think this is just how it's going to have to be.  I don't know how to force show "NED" for all products when what I am comparing against doesn't have a matched product in the first place.  It simply doesn't exist in those date ranges.

     

    Thanks for your help!  I really appreciate it.

     

    Regards,

    Derek

  • jderekc's avatar
    jderekc
    Helper IV
    8 years ago

    Hey guys, I got it fixed.  I simply combined my three queries into one using derived tables/subqueries.  Now all blanks show as "NEDs".  Thanks for your help!