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
Anonymous
Not applicable

DAX query to return true or false if text contains number

Hi 

I got a requirement where I am searching for the existence of number or not in a text and return true if numbers exist or false if don't exist

 

ID                   NumbersExist

abcdef                 False

abc123                 True

123456                 True

adcf2tr                  True

efghijk                  False

 

Not sure if there is a function available readily.

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

This would be easier to do in the query editor, but you asked for DAX.  Here is a column expression that gets your result.  Replace T2 with your actual table name.

 

Has Number =
VAR vNumbers = {
    "0",
    "1",
    "2",
    "3",
    "4",
    "5",
    "6",
    "7",
    "8",
    "9"
}
VAR vFiltered =
    FILTER (
        vNumbers,
        SEARCH (
            [Value],
            T2[ID],
            ,
            0
        ) > 0
    )
RETURN
    IF (
        COUNTROWS ( vFiltered ) > 0,
        "Y",
        "N"
    )

 
 
Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

7 REPLIES 7
Anonymous
Not applicable

@mahoneypat 

Thanks for your help and the solution you have provided did worked.

Has Number =
VAR vNumbers = { "0", "1", "2", "3", "4", "5", "6", "7", "8", "9" }
VAR vFiltered =
    FILTER (
        vNumbers,
        SEARCH ( [Value], 'TableName'[ColumnName],, 0 ) > 0
    )
RETURN
    IF ( COUNTROWS ( vFiltered ) > 0, "Y", "N" )

Anonymous
Not applicable

@mahoneypat 

I have tried the approach and it's not working 

 

found this long way

IF(OR(CONTAINSSTRING('User Security'[Principal Name],"0"),OR(CONTAINSSTRING('User Security'[Principal Name],"1"),OR(CONTAINSSTRING('User Security'[Principal Name],"2"),OR(CONTAINSSTRING('User Security'[Principal Name],"3"),OR(CONTAINSSTRING('User Security'[Principal Name],"4"),OR(CONTAINSSTRING('User Security'[Principal Name],"5"),OR(CONTAINSSTRING('User Security'[Principal Name],"6"),OR(CONTAINSSTRING('User Security'[Principal Name],"7"),OR(CONTAINSSTRING('User Security'[Principal Name],"8"),CONTAINSSTRING('User Security'[Principal Name],"9")))))))))),1,0)

 

Did you try it as a calculated column?  Did you replace T2[ID] with 'User Security'[Principal Name]?

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Thankyou @mahoneypat 

Will try the DAX version of solution and update here.

 

mahoneypat
Microsoft Employee
Microsoft Employee

If you decide to do in the query editor, you can use this in a custom column

 

= not List.IsEmpty(List.Intersect({Text.ToList([ID]), {"0".."9"}}))

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Microsoft Employee
Microsoft Employee

This would be easier to do in the query editor, but you asked for DAX.  Here is a column expression that gets your result.  Replace T2 with your actual table name.

 

Has Number =
VAR vNumbers = {
    "0",
    "1",
    "2",
    "3",
    "4",
    "5",
    "6",
    "7",
    "8",
    "9"
}
VAR vFiltered =
    FILTER (
        vNumbers,
        SEARCH (
            [Value],
            T2[ID],
            ,
            0
        ) > 0
    )
RETURN
    IF (
        COUNTROWS ( vFiltered ) > 0,
        "Y",
        "N"
    )

 
 
Regards,
Pat




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

HI @mahoneypat 

thanks for the response. it works properly.
what about the other way arround? instead of numbers, letters?
i will refer to my post, if you can answer:

 

https://community.powerbi.com/t5/Desktop/Formula-IF-value-contains-quot-number-quot-or-contains-quot...

 

Thanks

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.