topic Extracting String Using LEFT and SEARCH function in DAX Commands and Tips

Extracting String Using LEFT and SEARCH function

rosemilo — Wed, 11 May 2022 18:25:38 GMT

Hello,

I'm trying to extract the number before the colon (see example below) using DAX.

Here are the steps I did:

1. To return the specified number of characters I want (it worked)

c_Search Request Target Time = SEARCH(":", 'MyRequest_Consolidated'[Request Target Time],,LEN('MyRequest_Consolidated'[Request Target Time]))-1

2. To extract the number before the colon

c_Request Target Time = LEFT('MyRequest_Consolidated'[Request Target Time], MyRequest_Consolidated[c_Search Request Target Time])

The second formula returns the error below:

Is there any other way to extract the number I need before the colon (:)? The data type of the column is "Text". Thank you!

Re: Extracting String Using LEFT and SEARCH function

tamerj1 — Wed, 11 May 2022 18:40:39 GMT

rosemilo

Can you please provide sample data?

Re: Extracting String Using LEFT and SEARCH function

tamerj1 — Wed, 11 May 2022 18:49:43 GMT

Hi rosemilo

please try

c_Request Target Time = IF ( MyRequest_Consolidated[c_Search Request Target Time] > 0, LEFT ( 'MyRequest_Consolidated'[Request Target Time], MyRequest_Consolidated[c_Search Request Target Time] ) )

Re: Extracting String Using LEFT and SEARCH function

rosemilo — Wed, 11 May 2022 19:07:08 GMT

Thank you! It worked! 😊