Forum Discussion

JimmyBos's avatar
JimmyBos
Helper II
1 year ago
Solved

Using Multiple 'Substitute' in one formula

Hello All,

 

once more, and it looks like the last formula i would like to 'perfect' contains multiple 'Substitute'. Any idea how to change the formula into a bit easier to read?

 

Formula as it is:

Project clean = LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SELECTEDVALUE(POStuklijst[ParentProject])," ",""),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z",""),7)
Need Help
Show and Tell
  • Cookistador's avatar
    Cookistador
    1 year ago

    I see,

    it would be easier to do it in the other way, saying you only need the numerical value

    But to continue on the same approach, you just have to add 
    CharCode >= 97 && CharCode <= 122) to exclude the lowercase letter

    Project clean =
    VAR SourceText = SELECTEDVALUE('Table (2)'[Project])
    VAR TextLength = LEN(SourceText)
    VAR _Sequence = GENERATESERIES(1, 100)


    VAR FilteredCharacters =
        FILTER(
            _Sequence,
            VAR CurrentChar = MID(SourceText, [Value], 1) // Get character at current position
            VAR CharCode = UNICODE(CurrentChar) // Get Unicode value of the character
            RETURN
                NOT(
                    CharCode = 10 // Exclude space (Unicode 32)
                    || (CharCode >= 65 && CharCode <= 90) // Exclude A-Z (Unicode 65-90)
                    || (CharCode >= 97 && CharCode <= 122) // Exclude a-z (Unicode 97-122)
                )
        )

    VAR CleanedString =
        CONCATENATEX(
            FilteredCharacters,
            MID(SourceText, [Value], 1),
            "",
            [Value],
            ASC
        )

    RETURN
    IF(LEN(LEFT(CleanedString, 7)) = 0, BLANK(),LEFT(CleanedString, 7))

6 Replies

  • Cookistador's avatar
    Cookistador
    Super User
    1 year ago

    Hello JimmyBos 

     

    Does the following snippet help your ? (each time you have to remove letter or character, Unicode is a pretty good alternative)

    Project clean =
    VAR SourceText = SELECTEDVALUE('Table (2)'[Project])
    VAR TextLength = LEN(SourceText)
    VAR _Sequence = GENERATESERIES(1, 8)


    VAR FilteredCharacters =
        FILTER(
            _Sequence,
            VAR CurrentChar = MID(SourceText, [Value], 1) 
            VAR CharCode = UNICODE(CurrentChar) // Get Unicode value of the character
            RETURN
                NOT(
                    CharCode = 10 // Exclude space (Unicode 32)
                    || (CharCode >= 65 && CharCode <= 90) // Exclude A-Z (Unicode 65-90)
                )
        )

    VAR CleanedString =
        CONCATENATEX(
            FilteredCharacters,
            MID(SourceText, [Value], 1),
            "",
            [Value],
            ASC
        )

    RETURN
    LEFT(CleanedString, 7)
    • JimmyBos's avatar
      JimmyBos
      Helper II
      1 year ago

      Hello Cookistador , Thank you for showing me this formula. There are 2 small adjustments needed. The first issue is, when i add this formula to my visual, many extra rows pop up. There should be a line added like: 

      'IF( NOT(ISBLANK(???)),' I tried to get it working, but i do not know what to place on the '???'. The second is this formula has a result of 6 characters. There should be 7. (changing the '7' in your formula to '8' did not change the output.
      • Cookistador's avatar
        Cookistador
        Super User
        1 year ago

        Glad to be helpful

        This dax Code should solve this issue

        Project clean =
        VAR SourceText = SELECTEDVALUE('Table (2)'[Project])
        VAR TextLength = LEN(SourceText)
        VAR _Sequence = GENERATESERIES(1, 100)


        VAR FilteredCharacters =
            FILTER(
                _Sequence,
                VAR CurrentChar = MID(SourceText, [Value], 1) // Get character at current position
                VAR CharCode = UNICODE(CurrentChar) // Get Unicode value of the character
                RETURN
                    NOT(
                        CharCode = 10 // Exclude space (Unicode 32)
                        || (CharCode >= 65 && CharCode <= 90) // Exclude A-Z (Unicode 65-90)
                    )
            )

        VAR CleanedString =
            CONCATENATEX(
                FilteredCharacters,
                MID(SourceText, [Value], 1),
                "",
                [Value],
                ASC
            )

        RETURN
        IF(LEN(LEFT(CleanedString, 7)) = 0, BLANK(),LEFT(CleanedString, 7))
         
        This is what I got without the if

         



        And the reuslt with the IF

         

        Just be careful that a space count for one character, so if you have something like

        HELLO WORLD, you would have a blank line, due to the space