Extracting substrings: FIND throwing error despite being skipped by IF Statement

[PowerBINewbie7]

Hi all!

I'm trying to extract a column containing information (in this example: the numbers) from a column which looks something like this:

INFORMATION01

INFORMATION02

ABCDEFG Info: 03 HIJKLMNOPQR

I felt like putting something else here. Info: 04 And some more text!

This row contains a typo. Ifo: 05 Lorem ipsum dolor

My general idea is to create a DAX statement along the lines of:

"IF(CONTAINSSTRING(Table[column], "INFORMATION"), RIGHT(Table[column], 2),
IF(CONTAINSSTRING(Table[column], "Info: "), MID(Table[column], FIND("Info: ", Table[column])+7, 2), "Info not found")

Unfortunately, this causes the FIND function to throw an error for the whole column because not all rows contain "Info" and therefore, it could not return anything in those rows even if it wouldn't have to because CONTAINSSTRING would return false. Is there some way around this? (Eventually, I'd like to also account for typos with another IF-Statement)

[timalbers]

Ok this is strange.. I just simply copied your DAX logic and I am not able to reproduce the error.

[PowerBINewbie7]

Yup, my bad! I did not try my example code on this example, because I assumed the error would occur as it does in the table I'm actually working on... Sorry for wasting your time! I'll go back to figuring out what makes the table I'm working on different from my example.

[timalbers]

Not a problem at all

If there are still issues, just come back to this thread. 
I will try to help you best as I can

[PowerBINewbie7]

Thanks a lot!

As an update: For some reason, using SEARCH instead of FIND fixed it. I played around with my original table and "FIND"ing some strings worked fine, but not the ones I needed. The only explanation I can think of is that maybe FIND reacts to special characters (specifically: dots) in some way I was unaware of and SEARCH doesn't.

[v-yiruan-msft · Accepted solution]

Hi @PowerBINewbie7 ,

You can update the formula of calculated column as below:

Column = 
VAR _information =
    SEARCH ( "INFORMATION", 'Table'[Text], 1, 0 )
VAR _info =
    SEARCH ( "Info: ", 'Table'[Text], 1, 0 )
VAR _info1 =
    SEARCH ( "Ifo: ", 'Table'[Text], 1, 0 )
RETURN
    SWITCH (
        TRUE (),
        _information > 0, RIGHT ( 'Table'[Text], 2 ),
        _info > 0, MID ( 'Table'[Text], VALUE ( _info ) + 6, 2 ),
        _info1 > 1, MID ( 'Table'[Text], VALUE ( _info1 ) + 5, 2 ),
        "Info not found"
    )

Best Regards

[timalbers]

Glad, you found a way!
Only difference I know: FIND is case sensitive, SEARCH is not.
However, have a nice weekend!

[timalbers]

Hi @PowerBINewbie7,
could you please share a screenshot of the error message? I cannot reproduce the error.

One thing to mention besides that: If your tabele name really is "Table", you'll have to put it like this: 'Table'[column], because "Table" is a reserved word. Table[column] would return an error.

[PowerBINewbie7]

Hi @timalbers,

The error message is just this: 

with the column displaying "#ERROR" in each row.

My table is not actually named table, so that can't be the problem.

I've also tried replacing the MID-FIND part with just "Info found" and that works perfectly.