Count Blank values across related tables

[PanosIWA]

Please see attachment for table detail

I have three tables that are related.  I need to identify how many accounts (table Account) don't have a coresponding email address (table Interested Party).   Th attached image shows (in orange) how the tables are related, and in red what I am trying to calculate.

Also, I will be getting updated versions of these table periodically, if possible I want to setup in such a way where I can upload new versions of the 3 tables and all my calculations will be in place.  I need to be able to show progress as users are updating the system.  60% of the emails have been populated.  etc. 

Please help. 

[Mariusz]

Hi @PanosIWA 

Try something like this.

Count = 
CALCULATE(
    COUNTROWS( ac ), -- ac = your accounts table
    EXCEPT(
        VALUES( ac[AcId] ),
        CALCULATETABLE( 
            VALUES( 'Join'[AcId] ), -- Join = your midle (fact) table  
            NOT ISBLANK( email[email] ) --email= table where you store the emails
        )
    )
) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[PanosIWA]

Thanks for your help and response.  I put the below forumula into a new measure under the 'Account' table.  It seem to have took but when I drop the new measure into  a grid it returns 2 or 226.  I am trying to flag the 'Account_Number's with a y/n on if they have an email not.   Thanks again for your help and let me know if you need me to provide you with anything else to help.  I am fairly new and PBI. 

Count Email =

CALCULATE(

    COUNTROWS('402 EXTRACT - ACCOUNT'),

    EXCEPT(

        VALUES('402 EXTRACT - ACCOUNT'[ACCOUNT_NUMBER] ),

        CALCULATETABLE(

              VALUES('402 EXTRACT - ACCOUNT SUBMASTER XREF'[ACCOUNT_NUMBER]),

              NOT ISBLANK('402 EXTRACT - INTERESTED PARTY'[BI43_E_MAIL_ADDRESS])

            )

        )

    )

[Mariusz · Accepted solution]

Hi @PanosIWA 

I've attached a file where I have applied the measure from my earlier post.

In my example, it returns 2 names out of 4 that have no email attached.  

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn