Forum Discussion
DAX for Counting Rows Specific Words and Phrases in Long Text for a Card Visual
Hi Everyone,
I am trying to create DAX for a card visual that searches a long comment text field in the fact table and returns the number of rows that contain specific key words and phrases.I've tried to narrow the search by filtering first for the specific category so the entire table isn't searched. I've already created a DAX measure for counting all the rows in the fact table which is referenced in the DAX below
Below is the DAX I've come up with but it's not working.
Any suggestions would be greatly appreciated.
# Strikes = VAR Categ = FILTER('Work List', 'Work List'[Category Description] = "Management Failure")
VAR Phrases = FILTER('Work List','Work List'[Incident Text] IN{ "HITTING", "STRIKING","HIT", "STRUCK", "COMING INTO CONTACT WITH" })
RETURN
CALCULATE([Total # Delays],Categ,Phrases)
Yah, In doesn't work like that. You will need to do something like
# Strikes =
CALCULATE(
[Business Days (System) - Annual],
OR(
OR(
OR(
OR(
CONTAINSSTRING( 'Work List'[Incident Text], "HITTING" ),
CONTAINSSTRING( 'Work List'[Incident Text], "STRIKING" )
),
CONTAINSSTRING( 'Work List '[Incident Text], "HIT" )
),
CONTAINSSTRING( 'Work List'[Incident Text], "STRUCK" )
),
CONTAINSSTRING(
'Work List'[Incident Text],
"COMING INTO CONTACT WITH"
)
),
'Work List'[Category Description] = "Management Failure"
)If it were me, I would flag all the incident text you are trying to search for, in Power Query and using a new column. The change the measure to simply return the flag=1/Y or whatever.
If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!
5 Replies
AnthonyGenoveseResolver III
What isn't working with this formula?
It is returning a value of 0 - which is not correct.
- AnthonyGenovese
Try this. (And if it doesn't work, could you share an example of some of the table data? Screen shot would work. and your formula for [total # delays]. And have you verifired that [total # delays] works)
# Strikes =
CALCULATE([Total # Delays],Work List'[Incident Text] IN{ "HITTING", "STRIKING","HIT", "STRUCK", "COMING INTO CONTACT WITH" },
'Work List'[Category Description] = "Management Failure")
If this post was helpful, please kudos or accept the answer as a solution.
~ Anthony Genovese
Need more PBI help? PM me for affordable, dedicated training or consultant recomendations!
