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]
        )
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]
        )