Count row if cell contains text from another column table

[rigreen]

I have a data source where the column value of "errors" contains multiple error codes "EID01;EID03;EID04" etc. I have a seperate table with a column of the error codes. I'm trying to write a measure that will allow for a table to display all error codes and the count of codes within the "error" column. Simplified desired output below using a table visual.

Error Codes	Count of Errors
EID01	45
EID02	13
EID03	5

[Anonymous · Accepted solution]

Hi @rigreen ,

Suppose we have a table like this:

Split column in Power Query Editor:

Unpivot columns - > remove [attribute] column -> rename [value] column - close and apply:

Relationships:

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[tamerj1 · Accepted solution]

Hi @rigreen 
Please try

Count of Errors =
COUNTROWS (
    FILTER (
        Table1,
        CONTAINSSTRING ( Table1[errors], MAX ( Table2[Error Codes] ) )
    )
)

[rigreen]

Your solution worked and is also the one that I'm using. It won't let me switch it as the accepted solution. If I figure it out i'll change it back. Thank you!

[Anonymous · Accepted solution]

Hi @rigreen ,

Suppose we have a table like this:

Split column in Power Query Editor:

Unpivot columns - > remove [attribute] column -> rename [value] column - close and apply:

Relationships:

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[rigreen]

I should clarify, the rows in the column "errors" have multiple error codes per cell.