Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

If contains function with a filter

Hey guys, Trying to find a function which would allow me to count only rows which contain in a string of text 2 specific words: Cancelled or Expired. Cancelled could be found in  "Closed_Cancelled"...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    8 years ago

    Anonymous

     

    You can use SEARCH() in FILTER to achieve this kind of "instring" requirement. You can write your measure like below: 

     

    True Cancelled =
CALCULATE (
    DISTINCTCOUNT ( Jobs[Jobs ID] ),
    FILTER (
        ALL ( Jobs ),
        SEARCH ( "Cancelled", Jobs[Jobs Status],, BLANK () ) > 0
            || SEARCH ( "Expired", Jobs[Jobs Status],, BLANK () ) > 0
    )
)

    See my sample: 

     

     

     

    Regards,

v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

Anonymous

 

You can use SEARCH() in FILTER to achieve this kind of "instring" requirement. You can write your measure like below: 

 

True Cancelled =
CALCULATE (
    DISTINCTCOUNT ( Jobs[Jobs ID] ),
    FILTER (
        ALL ( Jobs ),
        SEARCH ( "Cancelled", Jobs[Jobs Status],, BLANK () ) > 0
            || SEARCH ( "Expired", Jobs[Jobs Status],, BLANK () ) > 0
    )
)

See my sample: 

 

 

 

Regards,