Check if email exisits across linked tables.

[PanosIWA]

I have 3 tables linked by 2 different fields as shown in the attached screen shot.  I need (a new column or measure, I think) that checks each account number, in the account table, to see if a corresponding email address exists....  in the Interested Party table. 

Yellow highlight shows link between Account and SubMaster tables

Orange highlight shows link between SubMaster and Interested Party tables

Please help.  Diagram attached.

[Anonymous · Accepted solution]

Hi @PanosIWA ,

Because you don't show your sample data, so I don't know what kind of data you have.

Assuming that you have the below kind of data which I created by myself based on your relationship.

I created a calculated column in the Submaster table and a calculated measure in the Account table to solve the problem.

Please try.

Column = LOOKUPVALUE(Party[EMAIL],Party[PAR_NUM],Submaster[PAR_NUM])

Measure 5 = IF(ISBLANK(CALCULATE(MAX(Submaster[Column]))),0,1)

Aiolos Zhao

[PanosIWA]

Thank you for  your response.  This apears to be working (hard to validate cause there are so many records.  The only problem I am having now is that I can't use the measure column in the account table to show (count) how many accounts have email vs. don't..   to track percentage of accounts with no email. 

[PanosIWA]

So here is some data (attached)  to better explain what I am after.   The two formulas you gave me worked well, but I cannot use the new measure on the account table to build a visual to total number of "Yes Emails" vs. "No Emails". 

I am tring to build a chart that shows each officer name and the number of accounts still without emails (outstanding count and maybe percentage of total, etc. ). 

Thanks so much again.  This is a huge help for me.  Let me know if i can provide you with anything else that would help.

[Anonymous]

Hi @PanosIWA ,

I added the OFFICE in my example data as the below screenshot, I think you just need to add one more measure, and then put the OFFICER and new measure into a bar chart, then it will show what you want, please try.

NO EMAIL = CALCULATE(DISTINCTCOUNT(Account[ACC_NAME]),FILTER(Account,[Measure 5] = "NO EMAIL"))

Aiolos Zhao

[PanosIWA]

Thank you so much.  I created the new measure as instructed which is returning a "1" for all "No Email" records.  However the field is not giving me the option to sum or count... it just shows 1 for each admin/Officer?  See attachment

[Anonymous]

you are using the wrong column for distinctcount function...

you need to use ACCOUNT_NUMBER, but not OFFICER_NAME...

Aiolos Zhao

[PanosIWA]

Ahhhh!  That did it.  You have been a life saver.  I may have a follow up when I recreate this for Phone#.  Because there could be a phone number in one of three different fields (home, mobil, work).  But I have enough to setup email an address.  Thank you so much again. 

[PanosIWA]

I repeated the same steps creating a new column and 2 new measures for the address field instead of the email field.  The numbers returned in the bar chart were identicle to the email numbers... so I created a grid and got the results attached, which are not correct.    What am I doint wrong?  Please help.

ColumnAddress1 = LOOKUPVALUE
           ('402 EXTRACT - INTERESTED PARTY'[BI3_LEGAL_ADDRESS_1],

          '402 EXTRACT - INTERESTED PARTY'[INTERESTED_PARTY_NUMBER],
            '402 EXTRACT - ACCOUNT SUBMASTER XREF'[INTERESTED_PARTY_NUMBER])

AA_MeasureAddress1 = IF(ISBLANK(CALCULATE(MAX(
                     '402 EXTRACT - ACCOUNT SUBMASTER XREF'[ColumnAddress1]))),
                     "No Address1", "Yes")

AA MeasureAddress1Calc = CALCULATE(DISTINCTCOUNT(
                         '402 EXTRACT - ACCOUNT'[ACCOUNT_NUMBER]), FILTER(
                         '402 EXTRACT - ACCOUNT',[AA_MeasureAddress1]
                          = "No Address1"))

  

[Anonymous]

Please show me some sample data so I can get more information.

Because basically if there data model is no change, it won't be wrong.

Aiolos Zhao

[Anonymous]

Glad to help you~