Forum Discussion

Iron Contributor

Sep 17, 2020

Solved

Help simplifying this DAX code

I am trying to find a way to simplify this code since it annoying to add nested characters and I think it inefficient when it comes to performance The point is, to leave only number 0-9 character...

BI & Data Analysis

excel

Formulas and Functions

SergeiBaklan

Sep 17, 2020

Ocasio27

To my knowledge in DAX there is no equivalent for sequential SUBSTITUTE() in such cases. As a comment, these days such transformation usually are doing in Power Query with M-script, that will be one short string.

As for DAX, I'd adjust formula a bit like

=
VAR phoneNumber=
   IF(
     ISBLANK([CELULAR]),
     [TELEFONO],
     [CELULAR]
    )
VAR cleanText=
   SUBSTITUTE (
     SUBSTITUTE (
       SUBSTITUTE (
         SUBSTITUTE(
           SUBSTITUTE(
             SUBSTITUTE(
               phoneNumber, "-", "" 
             ), "(", ""
            ),")",""
          )," ",""
        ),"/",""
      ),"\",""
    )
RETURN cleanText

JMB17

Bronze Contributor

Sep 17, 2020

If you have the textjoin function,

=TEXTJOIN("",TRUE,TEXT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),"0;;0;"))

The most current version of Excel may not require it, but you may need to hit Ctrl+Shift+Enter after copying/typing it into the formula bar as it is an array formula.

Ocasio27
Iron Contributor
Sep 17, 2020
JMB17

Thanks but I need it in DAX code
- SergeiBaklan
  Diamond Contributor
  Sep 17, 2020
  Ocasio27
  
  To my knowledge in DAX there is no equivalent for sequential SUBSTITUTE() in such cases. As a comment, these days such transformation usually are doing in Power Query with M-script, that will be one short string.
  
  As for DAX, I'd adjust formula a bit like
  
  = VAR phoneNumber= IF( ISBLANK([CELULAR]), [TELEFONO], [CELULAR] ) VAR cleanText= SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE ( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( phoneNumber, "-", "" ), "(", "" ),")","" )," ","" ),"/","" ),"\","" ) RETURN cleanText
  - Ocasio27
    Iron Contributor
    Sep 17, 2020
    SergeiBaklan
    
    Thank you.
    
    In this case, is there any way to remove non numerical characters? I am trying to extract only 0-9 characters