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 characters in the new column, because the people who input data there often make mistakes, and later format it as (###)###-####

 

My code:

 

=IF([CELULAR]="" , SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([TELEFONO], "-", ""), "(",""), ")", ""), " ", ""),"/",""), "\", "") , SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([CELULAR], "-", ""), "(", "") ,")","")," ",""), "/", ""), "\", ""))
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

10 Replies

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

    Hi Ocasio27,

    I don't know much about DAX but if you want to give it a shot with VBA, you can use this function.

    Function PhoneNumber(sWord As String)
    Dim sChar As String
    Dim x As Integer
    Dim sTemp As String

    sTemp = ""
    For x = 1 To Len(sWord)
        sChar = Mid(sWord, x, 1)
        If Asc(sChar) >= 48 And _
          Asc(sChar) <= 57 Then
            sTemp = sTemp & sChar
        End If
    Next
    PhoneNumber = "(" & Left(sTemp, 3) & ") " & Left(Right(sTemp, 7), 3) & "-" & Right(sTemp, 4)
End Function

     So if your "bastardized" number is in cell A1, you can type in cell B1

    =PhoneNumber(A1)

    You will probably want to add some sort of verification that there is 10 digit in the phone number though otherwise this won't work properly.

     

     

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

Resources