Count unique email addresses with filtering out x domain

[Anonymous]

Hi everyone,

I'm brand new here in the forum, but couldn't find a post/qestion that helped solve the issue I'm currently facing.

I have a column ('Practitioners'[PractitionerEmail]) that shows all the emails of our users/customers.

Now, I want to create a measure that counts the unique emails, but leaves out emails with our own domain (i.e. @gmail.com).

So far, I've done:

DISTINCTCOUNTNOBLANK('Practitioners'[PractitionerEmail]).

However, this returns all the unique email addresses, and I want to filter our email addresses that contains i.e. "gmail.com".

How do I do this?

Thanks!

[DataInsights]

@Anonymous,

Try this measure:

Unique Email Addresses = 
CALCULATE (
    DISTINCTCOUNTNOBLANK ( Practitioners[PractitionerEmail] ),
    FILTER (
        ALL ( Practitioners[PractitionerEmail] ),
        NOT CONTAINSSTRING ( Practitioners[PractitionerEmail], "@gmail.com" )
    )
)

[Anonymous]

Thank you so much DataInsights!

That helped, yes!

However, is it possible to add more filters than just "@gmail.com"? For instance if I want it to filter out both "@gmail.com" and "@hotmail.com" etc.?

I tried to add another line, but that didnt work😐

Unique Email Addresses =

CALCULATE (

    DISTINCTCOUNTNOBLANK ( Practitioners[PractitionerEmail] ),

    FILTER (

        ALL ( Practitioners[PractitionerEmail] ),

        NOT CONTAINSSTRING ( Practitioners[PractitionerEmail], "@gmail.com")

        NOT CONTAINSSTRING ( Practitioners[PractitionerEmail], "@hotmail.com")

    )

)

[DataInsights · Accepted solution]

@Anonymous,

You're close. Add a double ampersand (AND) and it should work:

Unique Email Addresses = 
CALCULATE (
    DISTINCTCOUNTNOBLANK ( Practitioners[PractitionerEmail] ),
    FILTER (
        ALL ( Practitioners[PractitionerEmail] ),
        NOT CONTAINSSTRING ( Practitioners[PractitionerEmail], "@gmail.com" )
            && NOT CONTAINSSTRING ( Practitioners[PractitionerEmail], "@hotmail.com" )
    )
)

[Anonymous]

Thank you so much!!!