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...
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.

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.