topic Re: CountRows where column A contains a string and column B = a string. in DAX Commands and Tips

CountRows where column A contains a string and column B = a string.

Anonymous — Thu, 18 Jun 2020 18:01:53 GMT

I have been driving myself crazy with this, and I've looked around and asked colleagues and I can't seem to get there. I am used to Power Apps syntax, but not DAX so I really need some help.

I have a table with many columns, two of which are important for a measure I am trying to create:

Table = Project Overview

Column 1 = Project Name

Column 2 = CP

I need to get the number of rows where: Project Name contains "Mission" AND CP = "Missing CP"

I got this far:

calculate( COUNT( 'Project Overview'[Project Name] ), CONTAINSSTRING('Project Overview'[Project Name],"Mission"))
But I can't figure out how to add a second condition in my filter. Can anyone help?

Re: CountRows where column A contains a string and column B = a string.

stevedep — Thu, 18 Jun 2020 18:13:11 GMT

Perhaps with AND(CONTAINSSTRING () ; CONTAINSSTRING ())

In the brackets you add your logic.

Kind regards Steve

Re: CountRows where column A contains a string and column B = a string.

mahoneypat — Thu, 18 Jun 2020 22:37:32 GMT

You can try an expression like this:

New Measure = COUNTROWS ( FILTER ( FILTER ( 'Project Overview', SEARCH ( 'Project Overview'[Project Name], "Mission",, 0 ) > 0 ), 'Project Name'[CP] = "Missing CP" ) )

The FILTERs are nested to improve performance if many rows (one column at a time).

If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.

Regards,

Pat

Re: CountRows where column A contains a string and column B = a string.

Anonymous — Fri, 19 Jun 2020 06:44:18 GMT

Thanks for this, unfortunately it gives me the 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"

Re: CountRows where column A contains a string and column B = a string.

Anonymous — Fri, 19 Jun 2020 06:47:35 GMT

Thanks Pat, for some reason it is showing 0 results, which is not true. My data definitely has rows where Project Name contains Mission and CO = "Missing CP"

Re: CountRows where column A contains a string and column B = a string.

Anonymous — Fri, 19 Jun 2020 07:27:02 GMT

Hi Anonymous ,

Try this measure

CP and Mission =

var a = CONTAINSSTRING(MAX('Table'[Project Name]), "Mission")

var b = FILTER('Table', a && MAX('Table'[CP]) = "CP")

RETURN

COUNTROWS(b)

Regards

Harsh Nathani

Re: CountRows where column A contains a string and column B = a string.

stevedep — Sun, 21 Jun 2020 19:27:51 GMT

Add a FILTER. And use &&

Re: CountRows where column A contains a string and column B = a string.

Katja262373 — Thu, 16 Nov 2023 18:09:59 GMT

Dear Mahoneypat,

thank you for your solution. I've tried to apply COUNTROWS /FILTER combination in my calculation, but it didn't work for me.. could you advise maybe, what do I do wrong?

thank you in advance for your help!

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Piechart-diagram-based-on-statistics-percentage-calculation/m-p/3537654#M135935
best regards,

Katia