SEARCH or IF(MID - Best practice for finding characters in Text string and return a definition.

[bilingual]

Hi , with your great help i have managed to create a formula which works fine for the most :

Type = SWITCH(

TRUE(),

SEARCH("S2OLDC",'Data'[Type],1,0) > 0 , "Working function",
SEARCH("LH",'Data'[Type],1,0) > 0 , "Standby function",

However contrary to a Substring formula, i am not able to lock the position, for example for row 2 , the "LH" values has to be in the first two characters (eks. Substr(Data'[Type],1,2)="LH"
What would you recommend?  - either switching to IF(MID and use it on all values or keep using SWITCH(SEARCH and only add IF(MID for the values where it will be a problem?

Or can you combine SEARCH and MID somehow?

[lkalawski · Accepted solution]

Hi @bilingual ,

All you have to do is check if Search returns 1, i.e. LH is in the first two places.
Change your measure to:

Type = SWITCH(
TRUE(),
SEARCH("S2OLDC",'Data'[Type],1,0) > 0 , "Working function",
SEARCH("LH",'Data'[Type],1,0) = 1 , "Standby function",

The search formula returns the index at which the search expression begins.

https://www.tutorialspoint.com/dax_functions/dax_search_function.htm 

_______________
If I helped, please accept the solution and give kudos! 😀

[bilingual]

Thanks al lot, it worked perfectly, i read your explanation, so the =1 "locks" that the first 2 characters needs be "LH", whereas >0 just indicate that "LH" has to be somewhere in the string?

[lkalawski]

@bilingual,

Yes, it is exactly as you wrote.

Regards

Lukasz

_______________
If I helped, please accept the solution and give kudos! 😀