SOLVED

Help simplifying this DAX code

%3CLINGO-SUB%20id%3D%22lingo-sub-1679264%22%20slang%3D%22en-US%22%3EHelp%20simplifying%20this%20DAX%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1679264%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20find%20a%20way%20to%20simplify%20this%20code%20since%20it%20annoying%20to%20add%20nested%20characters%20and%20I%20think%20it%20inefficient%20when%20it%20comes%20to%20performance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20point%20is%2C%20to%20leave%20only%20number%200-9%20characters%20in%20the%20new%20column%2C%20because%20the%20people%20who%20input%20data%20there%20often%20make%20mistakes%2C%20and%20later%20format%20it%20as%20(%23%23%23)%23%23%23-%23%23%23%23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3EIF%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%5BCELULAR%5D%3C%2FSPAN%3E%3CSPAN%3E%3D%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%5BTELEFONO%5D%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22-%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22(%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22)%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%26nbsp%3B%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%3C%2FSPAN%3E%3CSPAN%3E%22%2F%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3ESUBSTITUTE%3C%2FSPAN%3E%3CSPAN%3E(%3C%2FSPAN%3E%3CSPAN%3E%5BCELULAR%5D%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22-%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22(%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%26nbsp%3B%2C%3C%2FSPAN%3E%3CSPAN%3E%22)%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%3C%2FSPAN%3E%3CSPAN%3E%22%26nbsp%3B%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%2F%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E)%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%5C%22%3C%2FSPAN%3E%3CSPAN%3E%2C%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1679264%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680192%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20simplifying%20this%20DAX%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680192%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F438859%22%20target%3D%22_blank%22%3E%40Ocasio27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20familiar%20with%20DAX%20per%20se%2C%20but%20have%20the%20impression%20that%20many%20Excel%20features%20operate%20there%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20can%20you%20use%20Data%20Validation%20to%20ensure%20that%20what%20is%20entered%20is%20a%20number%2C%2010%20digits%20long%2C%20entered%20solely%20as%20numbers%2C%20and%20then%20let%20custom%20formatting%20take%20care%20of%20rendering%20it%20as%20(123)%20456-7890.%20That's%20what%20I'd%20do%20in%20Excel.%20The%20user%20would%20get%20an%20error%20message%20if%20they%20tried%20anything%20else.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680195%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20simplifying%20this%20DAX%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680195%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20get%20this%20data%20as%20an%20external%20feed%20directly%20in%20my%20Power%20Pivot%2C%20I%20cannot%20control%20the%20input%2C%20in%20fact%20I%20don't%20even%20know%20who%20are%20the%20people%20who%20does%20that%20input%20so%20what%20I%20need%20is%20a%20way%20to%20fix%20it%20in%20DAX%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680219%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20simplifying%20this%20DAX%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680219%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F438859%22%20target%3D%22_blank%22%3E%40Ocasio27%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20going%20to%20defer%20to%20others%20for%20a%20fix%20of%20that%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20got%20a%20difficult%20situation%2C%20from%20your%20description%2C%20and%20it%20may%20well%20be%20impossible%20to%20do%20what%20I'm%20going%20to%20suggest%2C%20but%20you%20surely%20realize%20that%20what%20you're%20doing%20is%20fixing%20something%20after%20it%20got%20broken.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wonder%20if%20there's%20any%20way--may%20take%20some%20time--to%20get%20to%20the%20front%20of%20the%20process%20and%20prevent%20the%20breakage%20in%20the%20first%20place.%20It's%20generally%20cheaper%20in%20the%20long%20run%20to%20design%20a%20process%20so%20as%20to%20prevent%20problems%20than%20it%20is%20to%20recover%20from%20them.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680501%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20simplifying%20this%20DAX%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680501%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F438859%22%20target%3D%22_blank%22%3E%40Ocasio27%3C%2FA%3E%2C%3C%2FP%3E%3CP%3EI%20don't%20know%20much%20about%20DAX%20but%20if%20you%20want%20to%20give%20it%20a%20shot%20with%20VBA%2C%20you%20can%20use%20this%20function.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EFunction%20PhoneNumber(sWord%20As%20String)%0A%20%20%20%20Dim%20sChar%20As%20String%0A%20%20%20%20Dim%20x%20As%20Integer%0A%20%20%20%20Dim%20sTemp%20As%20String%0A%0A%20%20%20%20sTemp%20%3D%20%22%22%0A%20%20%20%20For%20x%20%3D%201%20To%20Len(sWord)%0A%20%20%20%20%20%20%20%20sChar%20%3D%20Mid(sWord%2C%20x%2C%201)%0A%20%20%20%20%20%20%20%20If%20Asc(sChar)%20%26gt%3B%3D%2048%20And%20_%0A%20%20%20%20%20%20%20%20%20%20Asc(sChar)%20%26lt%3B%3D%2057%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20sTemp%20%3D%20sTemp%20%26amp%3B%20sChar%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%0A%20%20%20%20PhoneNumber%20%3D%20%22(%22%20%26amp%3B%20Left(sTemp%2C%203)%20%26amp%3B%20%22)%20%22%20%26amp%3B%20Left(Right(sTemp%2C%207)%2C%203)%20%26amp%3B%20%22-%22%20%26amp%3B%20Right(sTemp%2C%204)%0AEnd%20Function%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BSo%20if%20your%20%22bastardized%22%20number%20is%20in%20cell%20A1%2C%20you%20can%20type%20in%20cell%20B1%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DPhoneNumber(A1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bennadeau_0-1600369183859.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219283iDEA7D442920A66A9%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Bennadeau_0-1600369183859.png%22%20alt%3D%22Bennadeau_0-1600369183859.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EYou%20will%20probably%20want%20to%20add%20some%20sort%20of%20verification%20that%20there%20is%2010%20digit%20in%20the%20phone%20number%20though%20otherwise%20this%20won't%20work%20properly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1680547%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20simplifying%20this%20DAX%20code%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1680547%22%20slang%3D%22en-US%22%3EIf%20you%20have%20the%20textjoin%20function%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3DTEXTJOIN(%22%22%2CTRUE%2CTEXT(MID(A1%2CROW(INDIRECT(%221%3A%22%26amp%3BLEN(A1)))%2C1)%2C%220%3B%3B0%3B%22))%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20most%20current%20version%20of%20Excel%20may%20not%20require%20it%2C%20but%20you%20may%20need%20to%20hit%20Ctrl%2BShift%2BEnter%20after%20copying%2Ftyping%20it%20into%20the%20formula%20bar%20as%20it%20is%20an%20array%20formula.%3C%2FLINGO-BODY%3E
Highlighted
Frequent Contributor

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]"-"""), "(""") ,")","")," ",""), "/"""), "\"""))
10 Replies
Highlighted

@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.

Highlighted

@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

Highlighted

@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.

Highlighted

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)

Bennadeau_0-1600369183859.png

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.

 

 

Highlighted
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.
Highlighted

@JMB17 

 

Thanks but I need it in DAX code

Highlighted
Best Response confirmed by Ocasio27 (Frequent Contributor)
Solution

@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
Highlighted

@Sergei Baklan 

 

Thank you. 

 

In this case, is there any way to remove non numerical characters? I am trying to extract only 0-9 characters

Highlighted

@Ocasio27 

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

Highlighted

@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

image.png

With it calculated column B

image.png

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)