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

Ocasio27

Iron Contributor

Sep 17, 2020

mathetes

I get this data as an external feed directly in my Power Pivot, I cannot control the input, in fact I don't even know who are the people who does that input so what I need is a way to fix it in DAX

mathetes

Gold Contributor

Sep 17, 2020

Ocasio27

I'm going to defer to others for a fix of that code.

You've got a difficult situation, from your description, and it may well be impossible to do what I'm going to suggest, but you surely realize that what you're doing is fixing something after it got broken.

I wonder if there's any way--may take some time--to get to the front of the process and prevent the breakage in the first place. It's generally cheaper in the long run to design a process so as to prevent problems than it is to recover from them.

Forum Discussion

Help simplifying this DAX code

Resources