Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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)
Solved! Go to 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!
What isn't working with this formula?
It is returning a value of 0 - which is not correct.
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!
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 |
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!
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |