[DAX] Dynamic way to get numeric values [0..9] from string with no pattern.

[yel4h]

Hey, I have been wanting to make a DAX expression to solve this for a while.
I saw a lot of others also struggle to do this with DAX and would have to use Power Query to solve.

The following code will split the numberic value of your string eg, any where in your string. Good for if you are using calculated table i.e. creating the table using DAX. 

This can be use to get more number from more complex patterns or if your patterns varies, but it will get all of the numeric characters so if there is unwanted numberic characters you will need to alter.

EXAMPLE

your_text_field	get_digits
0 text/text 1	01
text 23 text 4	234
text.2.3.1xxx	231

get_digits= 
VAR txt = TRIM([your_text_field])

VAR digits_only =
    CONCATENATEX (
        FILTER (
            ADDCOLUMNS (
                GENERATESERIES ( 1, LEN ( txt ) ),
                "char", MID ( txt, [Value], 1 )
            ),
            MID ( txt, [Value], 1 ) >= "0"
                && MID ( txt, [Value], 1 ) <= "9"
        ),
        MID ( txt, [Value], 1 ),
        ""
    )

VAR make_value = IF ( LEN ( digits_only ) > 0, VALUE ( digits_only ), BLANK() )

RETURN make_value 

[v-karpurapud]

Hi @yel4h 

We have not received a response from you regarding the query and were following up to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank You.

[Poojara_D12]

Hi @yel4h 

This DAX expression is a clever way to extract all numeric characters from any text string directly within DAX, without needing Power Query transformations. It works by first trimming the text to remove unnecessary spaces, then using GENERATESERIES to create a list of positions from 1 to the length of the text. For each character position, it uses MID to extract the character and checks whether it falls between "0" and "9". Only characters that meet this numeric condition are kept using the FILTER function. Then, CONCATENATEX combines all these numeric characters back into a single string, preserving their original order. Finally, it converts the concatenated string into a number using the VALUE function — returning a numeric value if digits exist, or BLANK() if no digits are found.

This approach is particularly useful when building calculated columns or tables in DAX and you need to pull out numbers from mixed text patterns (e.g., IDs embedded in labels, product codes with numbers scattered in text, or inconsistent formatting). It’s a more dynamic alternative to Power Query’s “extract digits” transformations, especially if the text patterns vary and you want to stay within the DAX layer without modifying the data source. The only consideration is that it will extract all digits, so if there are unwanted numbers, you’d need to add extra logic to refine the extraction.

[danextian]

But once you split the string in a virtual table and just pick those with number equivalent, isn't TRIM unnecessary? Though it might make a difference performance-wise as there are lesser characters to split into.

But I don't get why use MID outside ADDCOLUMNS when you can just filter the calculated column inside the virtual table.

[yel4h · Accepted solution]

You are totally correct, I had comeback from a meeting and had forgot to change the code to refer to the virtual colum I created with addcolumn. 

My intial test which works but can become concumption heavy, esp when strings are lenghty: 

CONCATENATEX (
    FILTER (
        GENERATESERIES(1, LEN(txt)),
        MID(txt, [Value], 1) >= "0" &&
        MID(txt, [Value], 1) <= "9"
    ),
    MID(txt, [Value], 1),
    ""
)

[v-karpurapud]

Hi @yel4h 

Thank you for the update and for sharing the details of your initial approach. Could you please confirm if the issue has been fully resolved after using the virtual char column as suggested by @danextian ? If you have any further questions or need additional clarification, please feel free to let us know and we will be happy to assist you further.

Regards,
Microsoft Fabric Community Support Team

[Ahmedx]

This has already been written about a long time ago
https://www.antmanbi.com/post/extract-numbers-from-an-alphanumeric-string-using-dax

[grazitti_sapna]

Hi @yel4h,

Eveything seems fine, except the trim used in get_digits

Use below DAX

GetDigits_Measure =
VAR txt = MAX('Table'[your_text_field]) -- use MAX or SELECTEDVALUE to get the current row value
VAR digits_only =
CONCATENATEX(
FILTER(
ADDCOLUMNS(
GENERATESERIES(1, LEN(txt)),
"char", MID(txt, [Value], 1)
),
MID(txt, [Value], 1) >= "0" &&
MID(txt, [Value], 1) <= "9"
),
MID(txt, [Value], 1),
""
)
VAR make_value = IF(LEN(digits_only) > 0, VALUE(digits_only), BLANK())
RETURN
make_value

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[yel4h]

TRIM is to account for random white spaces people may have in their data it is not needed as you pointed out.
MAX is not used in the original code because this was not a measure, but calculated column. It is to be use in row context to clean data from creating a calcutlated table as I recommended. But it is helpful you mentioned this for those who wish to use it in a measure.