Creating a calculated table from other tables

[Anonymous]

I am trying to create some custom reporting that connects our campaigns with opportunities created.  We have a few methodologies we use but I am having difficulty getting the structure down and wondering if someone can help me with the first one.  Here is what a SQL query would look like with the outcome I need:

SELECT CampaignMember.Campaignid, Opportunity.id, CampaignMember.id

     FROM CampaignMember,Opportunity

     WHERE CampaignMember.CreatedDate < Opportunity.CreatedDate

    OUTER JOIN CampaignMember.Account_Id_Case_Safe__c = Opportunity.Account_Id_Case_Safe__c

My relationship looks like this below:

What I am trying to do is create a new table that will allow me to link opportunities to the campaign so I can ultimately report out like this:

Where I can show total amount of opportunities tied to each campaign given this approach.  Then if I wanted to, I could drill down to see which opportunities and accounts make up that amount (so I don’t just want to use a SUM function in the formula or a single calculated column).

Any help would be much appreciated as most of the help docs I have seen relate to calculated columns or an external SQL database.  Thanks in advance.

[amitchandak · Accepted solution]

@Anonymous , Try a new table like , make sure column name are not same if needed use selectcolumns to rename the column or reduce the columsn

Table = filter( generate(CampaignMember,Opportunity), [CampaignMember_Account_Id_Case_Safe__c] = [Opportunity_Account_Id_Case_Safe__c] && [CampaignMember_CreatedDate] < [Opportunity_CreatedDate])

I assumed renamed column

Another one is to merge table in power query using Account_Id_Case_Safe__c  and then create a column CampaignMember_CreatedDate < Opportunity_CreatedDate and filter true

merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

DAX Join: https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[Anonymous]

Thank you @amitchandak! I am sorry for the late reply but was out of office for a bit.  One last question, I am getting the following error:

The Column with the name of 'Id' already exists in the Table.

I noticed you said "I assume renamed column", but in all the documentation I cannot find where to mass apply a prefix or something to make each unique.  Since there are probably 20 fields that have shared naming in the tables, so doing it one by one seems like a pain.

Is there a way to apply:

• If fields are related to 'Campaign Member' table, prefix all fields with 'CampaignMember'
  • Result 'CampaignMemberID
  • etc
• If fields are related to 'Opportunity' table, prefix all fields with 'Opportunity'
  • Result 'OpportunityID'

Here is my current query

Calculated - Campaign vs Opp = filter( generate('Campaign Member',Opportunity), 'Campaign Member'[Account_ID_Case_Safe__c] = Opportunity[Account_Id_Case_Safe__c] && 'Campaign Member'[CreatedDate] < Opportunity[CreatedDate])