A DAX Measure to only display rows with comments that contains certain words.

[LRCSG]

Hello,
I have a Power BI table containing every comment I received from my customer survey. I am trying to create a DAX measure so that when I create a table visual, it will only show me comments that contain the words "spa," "facial," or "massage." It's not working, and I'm wondering if anyone can point me in the right direction or tell me where I'm going wrong. I would greatly appreciate any help I can get. Here's an example of the table and DAX.

Overall comments

The cashier was so friendly

the treatment I had was sooooooo good

I love the scent and product variety

I had a very welcoming experience

The facial I had gave me a glow

Best massage ever

Wow! they knew it was my birthday and gave me a small cake

DisplayComments :=
VAR Comment = SELECTEDVALUE('All Responses'[Overall Comment])
RETURN
IF (
NOT ISBLANK(Comment) &&
(
SEARCH("treatment", LOWER(Comment), 1, 0) > 0 ||
SEARCH("facial", LOWER(Comment), 1, 0) > 0 ||
SEARCH("spa", LOWER(Comment), 1, 0) > 0
),
Comment,
BLANK()
)

[techies]

Hi @LRCSG 

you can create a disconnected table keyword list table

and then adding a custom column in the power query editor
let
    keywords = Table.Column(KeywordList, "keyword"),
    comment = Text.Lower([Overall comments]),
    matchFound = List.AnyTrue(List.Transform(keywords, each Text.Contains(comment, Text.Lower(_))))
in
    if matchFound then "Yes" else "No"

Then you can use this column to filter this visual

[LRCSG]

Thank you @techies this is going to be useful for my reports. I didn't know we could do this.

[techies]

You're welcome!

[ThxAlot]

Easy enough,

[LRCSG]

Very useful to know how to build a keyword filter! Thank you 🙂

[v-dineshya]

Hi @LRCSG ,

Thank you for reaching out to the Microsoft Community Forum.

Option 1: Create a Calculated Column

ShowCommentFlag =
VAR Comment = LOWER('All Responses'[Overall Comment])
RETURN
IF (
SEARCH("spa", Comment, 1, 0) > 0 ||
SEARCH("facial", Comment, 1, 0) > 0 ||
SEARCH("massage", Comment, 1, 0) > 0,
1,
0
)
Then in your table visual, Add the Overall Comment column, Go to the Filters pane → drag in ShowCommentFlag,
Set it to "is 1".

Option 2: Use a Measure
If you need a measure because of other requirements (like aggregations):

DisplayComments :
VAR Comment = SELECTEDVALUE('All Responses'[Overall Comment])
RETURN
IF (
NOT ISBLANK(Comment) &&
(
SEARCH("spa", LOWER(Comment), 1, 0) > 0 ||
SEARCH("facial", LOWER(Comment), 1, 0) > 0 ||
SEARCH("massage", LOWER(Comment), 1, 0) > 0
),
Comment,
BLANK()
)
Then in your table visual, DisplayComments measure in the values area, Use a visual-level filter on DisplayComments → "is not blank".

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[LRCSG]

Thank you so much @v-dineshya ! This method also works and I can apply it to another one of my reports.

[Irwan · Accepted solution]

hello @LRCSG 

something like this?

create a new measure with following DAX.

Measure =
var _Desc = SELECTEDVALUE('Table'[Description])
Return
IF(
    CONTAINSSTRING(_Desc,"Spa")||CONTAINSSTRING(_Desc,"Facial")||CONTAINSSTRING(_Desc,"Treatment"),
    1,
    0
)

then put the measure into visual filter, and set value to 1.

Hope this will help.

Thank you.

[LRCSG]

Thank you @Irwan ! I feel like it's almost there. I'm now trying to figure out why that measure's filter on my Power BI is not editable/selectable.

[Irwan]

hello @LRCSG 

try to add a value in visual filter.

choose "is" then put 1 as value to show those values based on your measure.

Thank you.