DAX query to return true or false if text contains number

[Anonymous]

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.

[mahoneypat · Accepted solution]

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

[Anonymous]

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

[mahoneypat]

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

[Anonymous]

Thankyou @mahoneypat 

Will try the DAX version of solution and update here.

[Anonymous]

@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)

[mahoneypat]

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

Regards,

Pat

[Anonymous]

@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" )