Forum Discussion

JamesMF1982's avatar
JamesMF1982
Frequent Visitor
3 years ago
Solved

Using Search in a measure

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 ...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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"
        )
    )
)
TomasAndersson's avatar
TomasAndersson
Solution Sage
3 years ago

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's avatar
JamesMF1982
Frequent Visitor
3 years ago

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's avatar
    tamerj1
    Community Champion
    3 years ago

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