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

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

    Ocasio27 

    Unfortunately I don't know other ways with DAX-only but modifications of above formula.

Resources