cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
5 REPLIES 5
Super User
Hmm, I'm guessing it is the 0 padding that is throwing things off.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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.

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper III

Thanks a ton Greg

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors