Forum Discussion
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 =
COUNTROWS(
FILTER(WORKDAY_info,
CONTAINS(WORKDAY_info,WORKDAY_info[Job_Title],"Contingent") = TRUE()
)
)
Do I need to add wildcards or something?
HI joglidden
Try this one
M_Contractors = COUNTX( FILTER(WORKDAY_info, SEARCH("Contingent",WORKDAY_info[Job_Title],1,0) >0 ) , WORKDAY_info[Site_key] )
4 Replies
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.
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.
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]
)
