DAX Expression in PowerPivot to Remove Characters From the End of a String

Recently I needed to be able to remove a known string from the end of a text column in PowerPivot.  In my case, I had a client column, and in some cases the client column contained values with a colon and X on the end (such as “Bob’s Fishery:X”). 

Using the expression below, I was able to remove the “:X” from the end of the client column, and end up with just the client, as desired.

=LEFT([Client],IF (SEARCH(“:X”,[Client],1,0) > 0, SEARCH(“:X”,[Client],1,0), LEN([Client]))

This column converted “Bob’s Fishery:X” into “Bob’s Fishery”.

The inspiration from this came from this blog post http://parasdoshi.com/2013/11/13/example-of-an-dax-substring-equivalent/, where they extract the “username” out of “domainusername”.  In other words, the related blog post gets the latter portion of the string, whereas mine gets the former.

The expression in the related blog post is as follows

=RIGHT([UserID],LEN([UserID])-SEARCH(“”,[UserID]))

This will convert “domainbob” into “bob”.