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
    Icon for Memorable Member rankMemorable 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
    Icon for Microsoft Employee rankMicrosoft 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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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"

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        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