cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

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

6 REPLIES 6
Helper I
Helper I

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

Helper I
Helper I

@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


Helper I
Helper I

Thankyou @mahoneypat 

Will try the DAX version of solution and update here.

 

Super User IV
Super User IV

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


Super User IV
Super User IV

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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.