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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

I am trying to use if statement to calculate a column based off of 2 other columns.  There can be different combinations that the two columns can be to give me different outputs.  I shortened my dax statement below to get the point across, but I keep getting an error that says "Expressions that yield variant data-type cannot be used to define calculated columns".

 

Risk Category =
If(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Frequent", "Priority Risk",
if(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Occasional", "Priority Risk",
if(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Uncommon", "Recognized Risk",
if(ARPM[Severity]="Near Miss" && ARPM[Likelihood of Occurrence]="Remote", "Recognized Risk", 0))))

 

Please help!