Filtering by occurences in another table

[ReadTheIron]

I'm dealing with incident classifications and trying to find out how many times a given type of incident was misclassified, based on a table that has multiple followup comments per incident. For instance, in the example below, I'm trying to find out how many incidents with the cause "NONE" contained comments that mention water.

The tables are related on the WorkOrder field, one to many.

I've written a measure, but it only gives me the total number of WorkOrders containing "water" in the comments field, even if I try to break it down by CauseCode:

WaterCount=CALCULATE(DISTINCTCOUNT('CommentsTable'[WorkOrder]),FILTER(ALL('CommentsTable'),SEARCH("water",'CommentsTable'[Comments],,BLANK()) >0))

Table I do not want

[v-chenwuz-msft · Accepted solution]

Hi @ReadTheIron ,

Please try these two measure to do that.

Total Incidents =
DISTINCTCOUNT ( CommentsTable[WorkOrder] )

watercount =
CALCULATE (
    DISTINCTCOUNT ( CommentsTable[WorkOrder] ),
    FILTER ( 'CommentsTable', CONTAINSSTRING ( [Comments], "water" ) )
)

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ReadTheIron]

Thank you! This worked perfectly - and helped me identify a big problem with our data. You've really helped me out!

[ReadTheIron]

Does this work:

WorkOrder	CauseCode	Date
A001	Wind	1/1/2022
A002	None	1/2/2022
A003	Fire	1/3/2022
A004	None	1/3/2022
A005	Water	1/4/2022
A006	None	1/5/2022

WorkOrder	Comments
A001	Fixed wind trouble
A002	Problem not found
A002	Water leak
A003	Fixed fire trouble
A004	Water leak found
A004	Fixed water leak
A005	Fixed water leak
A006	Problem not found

[vanessafvg]

can you provide the data in text format?