Selective Text DAX For New Column

[cyborgandy]

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

[BA_Pete · Accepted solution]

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