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

[Anonymous]

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?

[mahoneypat]

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

[Katja262373]

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...
best regards,

Katia

[Anonymous]

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"

[harshnathani]

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

[stevedep]

Add a FILTER. And use &&

[stevedep]

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

In the brackets you add your logic.

Kind regards Steve

[Anonymous]

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"