Countrows if text is in one or the other column

[peetman]

Hi,

I have two columns where I want to find data and then count the number of rows found.

SW	PR
SW	SW
PR	SS
TR	TR

In the table above how can I count the number of rows where SW appears? Result expected would be 2.

I was trying to use the following, but successful.

Count SW covered = CALCULATE(COUNTROWS('Table1'),SEARCH("SW",OR('Col1','Col2'),,0))

Thanks

[Icey]

Hi @peetman ,

Please let us know if the latest reply of @mahoneypat could meet your requirements.

Best Regards,

Icey

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

[mahoneypat]

Please try this measure to get the rows with SW in them.  Replace SearchTable with your actual table name.

Count SW Rows = COUNTROWS(FILTER(SearchTable, "SW" in {SearchTable[Column1], SearchTable[Column2]}))

FYI you can use this expression to count the overall # of SW values.

Count SW = SUMX(SearchTable, COUNTROWS(FILTER({SearchTable[Column1], SearchTable[Column2]}, [Value] = "SW")))

Pat

[peetman]

Thanks. It does work.

I forgot to mention that sometimes is not explicitly written SW, but ASW or BSW. I've tried to use wildcards "*SW*" but it did not produce any result.

[mahoneypat · Accepted solution]

In that case, you can use the following:

Count SW Rows =
COUNTROWS (
    FILTER (
        SearchTable,
        SEARCH (
            "SW",
            SearchTable[Column1],
            ,
            0
        ) > 0
            || SEARCH (
                "SW",
                SearchTable[Column2],
                ,
                0
            ) > 0
    )
)

Pat

[pranit828]

Hi @peetman 

Count SW covered = CALCULATE(COUNTROWS('Table1'),SEARCH("SW",[Col1]) > 0  ||   SEARCH("SW",[Col2]) > 0)

[peetman]

Hello,

It does not work. Throws this error

The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.