Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

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?
CONTAINS
Filter Multiple Column

7 Replies

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

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

     

    In the brackets you add your logic.

    Kind regards Steve

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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"

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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