Many to Many assistance or validation of approach
I'm working with data from Dynamics CRM and for now trying to relate Contacts with the campaign they interacted with, not just their source campaign. The way our instance is getting populated I believe my best recourse is to use the lead record's 'parentContactID' to relate the Contacts to the Lead (1 to M). The Campaigns (one) are related to the (many) Leads by the Lead's CampaignID. This essentially makes the Lead Table the bridge table between Campaigns and Contacts. I've concocted a measure that appears to give me the number I need but I'm would like your help in knowing two things. Is this measure the appropriate way to identify number of contacts that interacted with a campaign Would it be better to have a separate bridge table (essentially a subset of columns from the Lead table) instead of relying on the Lead table itself. I've below is a subset of CRM data that I'm using to work with. as well as a screenshot of the relationships using the Leads table as the bridge table. Appreciate your assistance! My Measures: Campaign Contacts = VAR CampaignExistence = COUNTROWS( FILTER( 'Contacts_v2 Inquiry', COUNTROWS(RELATEDTABLE('Leads_v2 Inquiry')) ) ) RETURN CampaignExistence Campaign Leads = CALCULATE( COUNTROWS( 'Leads_v2 Inquiry' ) ) Contacts contactId Created By Created On Description originatingLeadId 1 CRM Service 6/9/2024 0:02 Project Name: #1 4 2 CRM Service 6/3/2024 10:00 Project Name: #2 3 Former Employee 2/23/2018 10:44 Inquiry: Hello- #3 2 4 CRM Service 6/3/2024 10:00 Project Name: #4 5 Former Employee 11/30/2017 9:22 Campaigns campaignid Name 1 Campaign 1 2 Campaign 2 3 Campaign 3 4 Campaign 4 5 Campaign 5 6 Campaign 6 7 Campaign 7 8 Campaign 8 9 Campaign 9 10 Campaign 10 11 Campaign 11 12 Campaign 12 13 Campaign 13 14 Campaign 14 15 Campaign 15 16 Campaign 16 17 Campaign 17 18 Campaign 18 19 Campaign 19 campaignId Description Full Name leadId parentAccountId parentContactId Project Name: #1 Lead 1 1 Inquiry: Hello- #1 Lead 2 2 1 3 18 Project Name:#2 Lead 3 3 4 18 Project Name: #3 Lead 4 4 1 # 4 Lead 5 5 18 Project Name: #5 Lead 1 6 2 #6 Lead 6 7 #7 Lead 7 8 18 Project Name: #8 Lead 1 9 2 Hi, #9 Lead 8 10Opportunity Win Rate of last year used as a prediction for next 6 months
I would like to calculate the Opportunities Win Rate (from Dynamics 365) of the last full year (i.e. YEAR minus 1) and us that ratio to weigh Esimated Revenues for the next six months. I already have a measures that simply calculates Win and Lost opportunities (using some CALCULATE and FILTER on YEAR -1). It works OK The problem arises when I try to use that measure to calculate Revenue for the next six Months, where in a Visual I use a "Relative Date" set to "Next 6 Months". It goes to zero, probably because I am "intersecting" somehting that goes back one year with a filter that show the next six months. How is it possible to NOT intersesct the filter in the visual with the actual measure and keep the measure the same no matter how I filter it?
