Forum Discussion

Velocity's avatar
Velocity
Helper III
6 years ago
Solved

Help with ISNUMBER function in DAX

Hi,

I am trying to extract month and year form two text fields. It is either embedded in field1 or field2. I have to try field one first and if it can't be extracted from field1, then it should be extracted from field2.

Field1           Field2                  ResultField

07184C         70007184c          0718

0RLET            0220RLET           0220

 

I am using following DAX command for calculated column resultfield

ResultField = if(
        isnumber(left(Sales[Field1],4)),
        left(Sales[Field1],4),
        left(Sales[Field2],4)
        )
It is always returning data from field2 which is not correct for first row.
And if i use:
ResultField = if(
        isnumber(value(left(Sales[Field1],4))),
        left(Sales[Field1],4),
        left(Sales[Field2],4)
        )
It is returning #error in column for both the rows.
 
Any help is greatly appreciated.
 
Thanks,
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Right, so ISNUMBER isn't working because it is testing if it is a number. It is not, it is text. You probably want:

     

    Column = 
    IF(
        ISERROR(VALUE(LEFT([Field1],4))),
        LEFT([Field2],4),
        LEFT([Field1],4)
    )

     

    See attached. 

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago
    Hmm, I'm guessing it is the 0 padding that is throwing things off.
    • Velocity's avatar
      Velocity
      Helper III
      6 years ago

      I don't think 0 padding is the issue.

       

      left(Sales[Field1],4) should return '0718' and why is that not being treated as number is the issue. Unfortunately, i have to use data as received.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Right, so ISNUMBER isn't working because it is testing if it is a number. It is not, it is text. You probably want:

         

        Column = 
    IF(
        ISERROR(VALUE(LEFT([Field1],4))),
        LEFT([Field2],4),
        LEFT([Field1],4)
    )

         

        See attached.