Return a value from an IF and Search

[Anonymous]

Hi all,

Completely stuck and hoping there is a quick answer. I need to return a three possible numerical values depending on the result of a search in another field. It's probably as simple to show you the formula I used in Excel that works perfectly:

=IF(OR(P2="RESOLVED",P2="CLOSED"),IF(ISNUMBER(SEARCH("Find this Text!",AR2)),10,-10),"")

It's for a case logging system and when the issue is initially resolved or eventually closed, a template should have been used. This scores the case based on whether that happened or not. It leaves the score blank if the case is still open.

Here's my version for a column in Power BI. Initial adjustments made to satisfy that I wanted the field to be numerical, so it should assign a "0" if the case is open. And I had to specify a 'value if not found':

Template = if(or(Data[State]="RESOLVED",SostenutoData[State]="CLOSED"),if(isnumber(search("Find This Text!",Data[Resolution Detail],,0)),10,-10),0)

It doesn't return an error, but it doesn't work either! No matter what I try it returns "10" for everything. It does catch 'open' cases and gives them "0", but I can't get it to tag the missing templates.

Any ideas would be much appreciated. 

Jon

[mattbrice]

Its because the way you have written SEARCH function, it will always return a number - either the string start position or zero so ISNUMBER is always true no matter what.  Change the 4th parameter in SEARCH function from 0 to BLANK() and it should work.

[Anonymous]

Thank you very much, guys. Both solutions worked perfectly!

I found a third one this morning too; create a column in the query that uses matching to return a value. I prefer to do it this way though as I'm still finding my feet in Power BI and this was always reliable in Excel.

All the best, Jon

[Zubair_Muhammad · Accepted solution]

@Anonymous

Give this a shot

Template =
IF (
    OR ( Data[State] = "RESOLVED", SostenutoData[State] = "CLOSED" ),
    IF ( SEARCH ( "Find This Text!", Data[Resolution Detail],, 0 ) > 0, 10, -10 ),
    0
)