DAX for Counting Rows Specific Words and Phrases in Long Text for a Card Visual

[MSargeant]

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)

[AnthonyGenovese]

What isn't working with this formula?

[MSargeant]

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!

[MSargeant]

Hi, it did not work. my formula for Total Delays: Total # Delays = COUNTROWS('Work List')

Below is a very modified portion of the table with applicable text relating to strikes:

Financial Year	Incident Create Date	Category Description	v_Delay Minutes	Incident Text
2020/21	30/01/2021	Management Failure	21	DESCRIPTION*** 30-JAN-2021 12:36:00 *** QWPCF03 *** *** 30/01/21 12:36 VEHICLE HAS STRUCK A BRANCH BETWEEN THE DRI VER WILL ATTEMPT TO MOVE THE BRANCH TO CONTINUE.  2:30 SWSC S SM ADVISES THAT THE BRANCH HAS BEEN CLEARED AND THE VEHICLES A RE ON THE MOVE.
2020/21	26/08/2020	Management Failure	2	DESCRIPTION*** 26-AUG-2020 15:28:00 *** QWPCF03 *** *** 26/08/20 15:28 #QWPCF03 *** CREATED ACY  VEGATATION 12:50 DRIVER REPORTED AN OVERHANGING BRANCH STRIKING VEHICLES. BRANCH CAME I NTO CONTACT WITH THE TOP OF VEHICLES. 12:52

[AnthonyGenovese · Accepted solution]

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!