Forum Discussion

yel4h's avatar
yel4h
New Member
11 months ago
Solved

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

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...
Show and Tell
Tips and Tricks
  • yel4h's avatar
    yel4h
    11 months ago

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




     

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
11 months ago

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's avatar
yel4h
New Member
11 months ago

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.