Forum Discussion

cyborgandy's avatar
cyborgandy
Helper II
4 years ago
Solved

Selective Text DAX For New Column

Hello All,

 

I am trying to write a DAX formula for a new column which pick only pick the charecters which are before space in other column, is it possible to perform this action

 

e.g

B37 7HQ    it should only show B37

EC1A 7AJ    it should only show EC1A

S4 7TL        it should only show S4

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    Hi cyborgandy ,

     

    In DAX it would be something like this:

     

    _textBeforSpace =
LEFT(
    SUBSTITUTE(
        yourTable[value],
        " ",
        "-"
    ),
    SEARCH(
        "-",
        SUBSTITUTE(
            yourTable[value],
            " ",
            "-"
        )
    ) - 1
)

     

     

    Or perhaps this might work for you:

    TRIM(
    LEFT(
        SUBSTITUTE(
            yourTable[value],
            " ",
            REPT(" ",LEN(yourTable[value]))
        ),
        LEN(yourTable[value])
    )
)

     

    However, it's much easier to do in Power Query if available using Text.BeforeDelimiter.

     

    Pete

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi cyborgandy ,

     

    In DAX it would be something like this:

     

    _textBeforSpace =
LEFT(
    SUBSTITUTE(
        yourTable[value],
        " ",
        "-"
    ),
    SEARCH(
        "-",
        SUBSTITUTE(
            yourTable[value],
            " ",
            "-"
        )
    ) - 1
)

     

     

    Or perhaps this might work for you:

    TRIM(
    LEFT(
        SUBSTITUTE(
            yourTable[value],
            " ",
            REPT(" ",LEN(yourTable[value]))
        ),
        LEN(yourTable[value])
    )
)

     

    However, it's much easier to do in Power Query if available using Text.BeforeDelimiter.

     

    Pete