cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fabianhenzler Frequent Visitor
Frequent Visitor

Counting on another Table that are related through the email domain

I have 3 Tables Accounts, Opportunities and Leads.

Opportunities and Accounts are related.

 

Accounts have 3 email fields email, email1 and email2.

Leads have 1 email field called email.

 

I need to add 3 columns to Opportunities from the Leads by comparing the email domain. If the email domain after the "@" symbol matches in one of the email fields Accounts[email], Accounts[email1] or Accounts[email2] match the domain of a Leads[email] than I want to add the Leads[id] and the Leads[Campaign] to the Opportunity - and also add a count of leads to the Opportunity.

If more than 1 lead matches the Opportunity's email domain, than I'd like to just the the latest Leads[date_modified] for the LeadId and the LeadCampaign.

 

if anyone could help me with this issue I'd be super happy 🤓🤗☺️

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Counting on another Table that are related through the email domain

@fabianhenzler,

Firstly, create the following calculated columns in your Accounts table and Leads table.

Emaildomain = RIGHT(Accounts[email],LEN(Accounts[email])-FIND("@",Accounts[email]))

Email1domain = RIGHT(Accounts[email1],LEN(Accounts[email1])-FIND("@",Accounts[email1]))

Email2domain = RIGHT(Accounts[email2],LEN(Accounts[email2])-FIND("@",Accounts[email2]))

Emaildom = RIGHT(Leads[email],LEN(Leads[email])-FIND("@",Leads[email]))

Secondly, assume that you have any matching rows of fields in Opportunities and Leads. Create the following columns in your Opportunities table.

LeadsID = IF(RELATED(Accounts[Emaildomain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email1domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email2domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id]),LOOKUPVALUE(Leads[id],Leads[index],Opportunities[id]),"")

 

Leadscam = IF(RELATED(Accounts[Emaildomain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email1domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email2domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id]),LOOKUPVALUE(Leads[Campaign],Leads[index],Opportunities[id]),"")

 

Countleads = COUNTROWS(Leads)


Thirdly, please describe more details about that "If more than 1 lead matches the Opportunity's email domain, than I'd like to just the the latest Leads[date_modified] for the LeadId and the LeadCampaign".

And if the above formulas don't help, please post sample data of the three tables and post expected result here. Also we need to know which field you use to create relationship between Opportunity and Accounts.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Counting on another Table that are related through the email domain

@fabianhenzler,

Firstly, create the following calculated columns in your Accounts table and Leads table.

Emaildomain = RIGHT(Accounts[email],LEN(Accounts[email])-FIND("@",Accounts[email]))

Email1domain = RIGHT(Accounts[email1],LEN(Accounts[email1])-FIND("@",Accounts[email1]))

Email2domain = RIGHT(Accounts[email2],LEN(Accounts[email2])-FIND("@",Accounts[email2]))

Emaildom = RIGHT(Leads[email],LEN(Leads[email])-FIND("@",Leads[email]))

Secondly, assume that you have any matching rows of fields in Opportunities and Leads. Create the following columns in your Opportunities table.

LeadsID = IF(RELATED(Accounts[Emaildomain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email1domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email2domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id]),LOOKUPVALUE(Leads[id],Leads[index],Opportunities[id]),"")

 

Leadscam = IF(RELATED(Accounts[Emaildomain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email1domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id])||RELATED(Accounts[Email2domain])=LOOKUPVALUE(Leads[Emaildom],Leads[index],Opportunities[id]),LOOKUPVALUE(Leads[Campaign],Leads[index],Opportunities[id]),"")

 

Countleads = COUNTROWS(Leads)


Thirdly, please describe more details about that "If more than 1 lead matches the Opportunity's email domain, than I'd like to just the the latest Leads[date_modified] for the LeadId and the LeadCampaign".

And if the above formulas don't help, please post sample data of the three tables and post expected result here. Also we need to know which field you use to create relationship between Opportunity and Accounts.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 339 members 3,722 guests
Please welcome our newest community members: