Dax Improvement/Optimization Question

[Anonymous]

I have a question where I am looking for another way to write this DAX measure.

This involves two tables. Table 1 is Accounts and Table 2 is Contacts. The relationship is one to Many (Accounts to Contacts).

I am looking to get a total count of Accounts where all of their Contacts do not have an email.

My dax so far: 

CALCULATE(DISTINCTCOUNT(accounts[accountid]),contacts[emailaddress] = BLANK(),CROSSFILTER(contacts[accountid],accounts[accountid],Both),FILTER(dyn_accounts,[Total_Contacts] = [Total_Contacts_Missing_Email]))

The hardest part I think is the last part about where we need to only count accounts that have all of their contacts missing an email. The way I get around that is by creating two measures outside this one comparing an account total contacts to their total contacts missing an email.

Any improvement suggestions? 

[v-jingzhang · Accepted solution]

@Anonymous , you may try the following method: 

First add a column to contacts table:

HasEmailAddress =
CALCULATE (
    IF (
        MAXX (
            contacts,
            IF ( ISBLANK ( contacts[emailaddress] ) || contacts[emailaddress] = "", 0, 1 )
        ) = 1,
        1,
        0
    ),
    ALLEXCEPT ( contacts, contacts[accountid] )
)

Then create a measure and put it in a card to display it.

Count of no email = 
CALCULATE (
    DISTINCTCOUNT ( contacts[accountid] ),
    contacts[HasEmailAddress] = 0
)

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[DataInsights]

@Anonymous,

Try this measure:

Accounts without Email = 
VAR vAccount =
    MAX ( Accounts[Account ID] )
VAR vContacts =
    FILTER (
        ALL ( Contacts ),
        Contacts[Account ID] = vAccount
            && NOT ISBLANK ( Contacts[Email] )
    )
VAR vResult =
    IF ( COUNTROWS ( vContacts ) > 0, 0, 1 )
RETURN
    vResult

The matrix/table visual should use Account ID from the Accounts table.