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 should return the numerical data.  I don't care if the data is actually stored as a number or a string, however, as I am just displaying it in a card in Power BI desktop.  Though the data IS numerical in previous calculations (hence why this final calculation is called "Calc4_qOH").  Here's my query:

 

EstQtyOH = IF(InvAnalytics[Calc4_qOH] < 2, "NED", InvAnalytics[Calc4_qOH])

 

I receive the error "Expressions that yield variant data-type cannot be used to define calculated columns."

 

If I change "NED" to a number, the IF statement works.  I tried SWITCH as well, but didn't get any further than if I used IF.  Am I doing something wrong and my request is possible, or is my request impossible for DAX?  Again, I am still learning DAX as I go.  Many thanks in advance!

 

- Derek

 

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

18 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey, you can't use in DAX, but you can use a DAX statement, that explicitly converts the numeric value to text, like so

    IF(
	'Table1'[Column1] >= 2, FORMAT('Table1'[Column1],"#"), "too few")

    Hope this helps

     

    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

  • 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!