CountROW contains multiple text strings - single column

[stvitus]

Greetings, 

My data set concatenates multiple field values into a single cell where they appear in the same record. However, there are two variants of each value.  

Trying to create a count measure of rows that contain one value, another value or both. For instance, a five count of rows that contain Blue Car or Blue Truck, and omit Blue Van. 

Record No.	Product
875534	Blue Truck
875535	Blue Car, Blue Truck
875536	Blue Van
875537	Red Car, Blue Truck
875538	Red Car, Red Van
875539	Blue Car, Red Truck
875540	Blue Car

I've been looking for hours and found a filter query that would return the cells containing one value (rows 1 and 7), but none of the combined ones. Ive been working with CONTAINSSTRING, but can't fugure out how to add add second variable.  

Thank you!

[ThxAlot · Accepted solution]

[tamerj1]

Hi @stvitus 

First you need to manually create a disconnected table that contains all the unique values ("Blue car", "Ref Truck", ect.) to be used as a slicer. Then the CountMeasure would be 

CountMeasure =
COUNTROWS (
FILTER (
'Table',
NOT ISEMPTY (
FILTER ( SlicerTable, CONTAINSSTRING ( 'Table'[Product], SlicerTable[Value] ) )
)
)
)

you can also place it in the filter and filter is 1 or is not 0