countrows, filter, contains

[joglidden]

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 =
    COUNTROWS(
        FILTER(WORKDAY_info,
            CONTAINS(WORKDAY_info,WORKDAY_info[Job_Title],"Contingent") = TRUE()
        )
    )

Do I need to add wildcards or something? 

[Phil_Seamark]

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]

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

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 · Accepted solution]

HI @joglidden

Try this one

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