Help with ISNUMBER function in DAX

[Velocity]

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]

Hmm, I'm guessing it is the 0 padding that is throwing things off.

[Velocity]

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 · Accepted solution]

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. 

[Velocity]

Thanks a ton Greg

[Greg_Deckler]

My pleasure! Those kinds of nit things can be maddening! 🙂