Forum Discussion
CountROW contains multiple text strings - single column
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!
2 Replies
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
