Extracting String Using LEFT and SEARCH function

[rosemilo]

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!

[tamerj1 · Accepted solution]

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]
    )
)

[rosemilo]

Thank you! It worked! 😊

[tamerj1]

@rosemilo 

Can you please provide sample data?