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

  • JimmyBos's avatar
    JimmyBos
    Helper II
    1 year ago

    Hello Cookistador , This fixed both issues, thanks! Just noticed there small issue left.

     

    First i would like to show you what i am filtering with the formula you gave me. Everything related to a project we name something like PR2500042.01 or PR2500042.01 or I2500042 or PRVH2500042. All belong to project 2500042. The formula will tell me which project something is related to. See screenshot below.

     

    If for example PR2500042.01 was written with small letters (pr2500042.01) i would still like to get the same outcome: 2500042. Would your formula give me the same outcome?

    • Cookistador's avatar
      Cookistador
      Super User
      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))
      • JimmyBos's avatar
        JimmyBos
        Helper II
        1 year ago

        Cookistador  Thanks a lot for all the help, all works perfect!