Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
)
)
)
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.
Hi, I have tried this but when I use the CONTAINSSTRING option, the column I am trying to reference is not available for selection.
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"
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |