Forum Discussion

joglidden's avatar
joglidden
Icon for Advocate III rankAdvocate III
8 years ago
Solved

countrows, filter, contains

Can anyone tell me why this measure doesn't work? It returns (Blank), rather than 1830, as it should, and as the table with a visual-level filter with 'contains' does...   M_Contractors =     COUN...
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    HI joglidden

     

    Try this one

     

    M_Contractors =
   COUNTX(
       FILTER(WORKDAY_info,
           SEARCH("Contingent",WORKDAY_info[Job_Title],1,0) >0
           )
           ,
           WORKDAY_info[Site_key]
        )
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi joglidden

 

Are you trying to do string matching?  eg. to find rows that have the letter "B" in a set of text like "ABC" ?

 

If so you need to use either FIND or SEARCH.  FIND is case sensitive while SEARCH isn't.

 

 

joglidden's avatar
joglidden
Icon for Advocate III rankAdvocate III
8 years ago

Phil_Seamark, thanks for the response. I'd simply like to count the rows that contain "Contingent" in the field WORKDAY_info[Job_Title]. FIND and SEARCH return an integer position value, whereas CONTAINS returns a true/false. 

I thought my expression made perfect logical sense, but obviously DAX don't work that way. 

 

There just isn't the equivalent of SQL LIKE in DAX. 

  • joglidden's avatar
    joglidden
    Icon for Advocate III rankAdvocate III
    8 years ago

    So, here's something that did work. But I don't trust it, because it all depends on "Contingent" being in position 1. What if there is a space? Anyone have any suggestions for improvement?

     

    M_Contractors =
       COUNTX(
           FILTER(WORKDAY_info,
               FIND("Contingent",WORKDAY_info[Job_Title],1,0) = 1
               )
               ,
               WORKDAY_info[Site_key]
            )

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      HI joglidden

       

      Try this one

       

      M_Contractors =
   COUNTX(
       FILTER(WORKDAY_info,
           SEARCH("Contingent",WORKDAY_info[Job_Title],1,0) >0
           )
           ,
           WORKDAY_info[Site_key]
        )