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

mathetes

Gold Contributor

Sep 17, 2020

Ocasio27

I'm not familiar with DAX per se, but have the impression that many Excel features operate there as well.

So can you use Data Validation to ensure that what is entered is a number, 10 digits long, entered solely as numbers, and then let custom formatting take care of rendering it as (123) 456-7890. That's what I'd do in Excel. The user would get an error message if they tried anything else.

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.