DAX: Discount Count Field does not contain a particular string

[Anonymous]

Hi Experts,

Below is my sample data. I need to the count distinctvalues of staff Name who haven't resigned. 

Please helpe me!

Thank you

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try this:

Measure = 
VAR _t =
    FILTER (
        VALUES ( 'Table'[Staff Name] ),
        CONTAINSSTRING ( [Staff Name], "Resigned" ) = FALSE ()
    )
RETURN
    COUNTROWS ( _t )

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[goncalogeraldes]

Hello there @Anonymous ! 

Not Resigned =
var _select = SELECTEDVALUE(Table[Staff Name])
var _valid = RIGHT(_select, 10)
var _count = DISTINCTCOUNT(Table[Staff Name])

return
IF(
_valid <> "(Resigned)",
_count)

Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[Anonymous]

Hi @goncalogeraldes 

Thank you very much for your quick help. I have applied your DAX, but i am not getting the correct result. The correct result should be 4. 

Below is the snap of shot.

[goncalogeraldes]

Hello there @Anonymous ! What about this one?

Not Resigned =
var _select = SELECTEDVALUE(Table[Staff Name])
var _valid = RIGHT(_select, 10)
var _count = DISTINCTCOUNT(Table[Staff Name])

return
CALCULATE(
     _count,
     FILTER( 
           RIGHT( _select) <> _valid )
)

 Hope this answer solves your problem! If you need any additional help please @ me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes