Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Velocity
Helper III
Helper III

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,
1 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. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Community Champion
Community Champion

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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.

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. 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Thanks a ton Greg

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.