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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Employee
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
Employee
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
Employee
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors