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