Using Search in a measure

[JamesMF1982]

HI all,

I'm hoping someone can help me. I have a column (RESIDENT[AdminType]) that has a load of varying data in there which represents a number of settings. For example "NurseLABench" that will mean Nursing,  Local Authority, benchmark or FrailPCTNegot which will mean Frail, PCT and Negotiate

What I want to do is search the column and return full words based on found words in the phrase. I can't use a column due to restrictions so have to use a measure.

I thought something like SWITCH would work with SEARCH, but I can't get anything to stick.

Any help would be amazing.

Thanks

[TomasAndersson]

What would you like the result to be if several words exist? A measure with several full words after each other, like "Nursing Local Authority Benchmark" or something like that?

I think SWITCH() is not perfect in this case since you want to test for each possible word and possibly return several words.

One way (although impractical depending on the number of possible words) would be to do string several either IF(SEARCH()) or maybe even better IF(CONTAINSSTRING()) and test for each word. If the word exists you return the word string, and if not return blank. You would also decide on how to do punctuation/spacing if you have several matches.

result = 
if(CONTAINSSTRING(RESIDENT[AdminType],"Nurse"),"Nursing ","") &
if(CONTAINSSTRING(RESIDENT[AdminType],"LA"),"Local authority ","") 

And so on. Could work.

[JamesMF1982]

Hi, I have tried this but when I use the CONTAINSSTRING option, the column I am trying to reference is not available for selection.

[tamerj1 · Accepted solution]

Hi @JamesMF1982 
If you are creating a measure then you may try

Key Word =
IF (
    HASONEVALUE ( RESIDENT[AdminType] ),
    CONCATENATEX (
        VALUES ( RESIDENT[AdminType] ),
        SWITCH (
            TRUE (),
            CONTAINSSTRING ( RESIDENT[AdminType], "Nurse" ), "Nursing",
            CONTAINSSTRING ( RESIDENT[AdminType], "LA" ), "Local Authority"
        )
    )
)

[tamerj1]

Hi @JamesMF1982 

you can use SWITCH along with CONTAINSSTRING