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.

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.