Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
@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.
@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.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |