topic Re: Help with ISNUMBER function in DAX in DAX Commands and Tips

Help with ISNUMBER function in DAX

Velocity — Fri, 10 Apr 2020 11:18:33 GMT

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,

Re: Help with ISNUMBER function in DAX

Greg_Deckler — Fri, 10 Apr 2020 11:20:50 GMT

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

Re: Help with ISNUMBER function in DAX

Velocity — Fri, 10 Apr 2020 11:40:35 GMT

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.

Re: Help with ISNUMBER function in DAX

Greg_Deckler — Fri, 10 Apr 2020 11:54:48 GMT

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.

Re: Help with ISNUMBER function in DAX

Velocity — Fri, 10 Apr 2020 12:24:03 GMT

Thanks a ton Greg

Re: Help with ISNUMBER function in DAX

Greg_Deckler — Fri, 10 Apr 2020 12:43:51 GMT

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