Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dax Improvement/Optimization Question

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? 

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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.

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    5 years ago

    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.