Extract the value after the last occurrence of space in Power BI DAX

Question

I have data like this:

• lm-sample prod
• lm sample prod
• lm-exit nonprod-shared
• lm- value dev

I want to extract just the last value after the first space from right. So in this example:

• prod
• prod
• nonprod-shared
• dev

I tried:

Env = 
Var FirstSpace = FIND(" ", 'AWS Reservations'[Account])
Return RIGHT('AWS Reservations'[Account],FirstSpace - 1)

But that is giving me odd results. Would appreciate some help on solving this. Thanks.

Answer 1

The lack of options for FIND or SEARCH to search from the end of the string makes this quite tricky.

You can use:

Env = TRIM(RIGHT(SUBSTITUTE('AWS Reservations'[Account], " ", REPT(" ", LEN('AWS Reservations'[Account]))), LEN('AWS Reservations'[Account])))

Or break it down for better understanding:

Env = 
VAR string_length = LEN('AWS Reservations'[Account])
RETURN
TRIM(
    RIGHT(
        SUBSTITUTE(
            'AWS Reservations'[Account],
            " ",
            REPT(" ", string_length)
        ),
        string_length
    )
)

Take lm-sample prod as an example.

First, we use REPT(" ", string_length) to create a string of

"              "

which has the same length as the value lm-sample prod.

Then, we substitute all the occurrences of " " with this extra long

"              "

and the string will become

lm-sample              prod

After that, we'll be comfortable getting the substring of string_length from the right, i.e.

"          prod"

Finally, we trim the result to get what we want, prod.

Results:

Reference