Forum Discussion

Ocasio27's avatar
Ocasio27
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's avatar
    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's avatar
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's avatar
Ocasio27
Iron Contributor
Sep 17, 2020

JMB17 

 

Thanks but I need it in DAX code

  • SergeiBaklan's avatar
    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's avatar
      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

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Sep 22, 2020

        Ocasio27 

        There is a variant. Not sure how it'll be from performance point of view, however. Let add helper table ID to the data model

        With it calculated column B

        could be as

        =VAR source=Table2[A]
VAR charactersTable=
FILTER (
  SELECTCOLUMNS (
     'ID',
     "Value",'ID'[ID],
      "mchar",MID(source,'ID'[ID],1),
      "mcode", UNICODE(MID(source,'ID'[ID],1))
  ),
  [mcode] <58 && [mcode] >47
)
VAR Result= CONCATENATEX(charactersTable, [mchar]) 
Return Result

        (48 is ascii code for zero, and 57 for 9)

Resources