Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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()
)
Solved! Go to 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
)
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
Thank you @techies this is going to be useful for my reports. I didn't know we could do this.
You're welcome!
Very useful to know how to build a keyword filter! Thank you 🙂
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
Thank you so much @v-dineshya ! This method also works and I can apply it to another one of my reports.
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
)
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.
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |