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 h...
CONTAINS
Filter Multiple Column
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
      • stevedep's avatar
        stevedep
        Icon for Memorable Member rankMemorable Member
        6 years ago

        Add a FILTER. And use &&