Forum Discussion
Help - Turning SQL into DAX to attribute SFDC opportunities to SFDC campaign members
Hello - hoping one of teh DAX wizards out there can help me with the folllowing: I am going from Domo to PowerBi, transfering SQL to DAX and am having a very hard time attributing SDFC opportunities to SFDC campaigns based on a SFDC campaign member, being a member of the account that the opportunity was created from. The way the current SQL query works is by looking for any SFDC opportunites that have a created date greater than or equal to the SDFC campaing start date, and then checking to see if any member of the account that the opportunty was created from, is also a member of the SDFC campaign. I am posting the SQL query below as well as an initial result I got from ChatGPT. The ChatGPT DAX expression seems logical but retursn errors for me when I try it in my semantic model.
SQL query:
SELECT
cr.Campaign Name
FROM sf_extracted_campaign_date AS cr
WHERE cr.SF 18 ID = o.Account ID.SF 18 ID
AND cr.sf_extracted_campaign_date < o.Created Date
ORDER BY cr.sf_extracted_campaign_date DESC
LIMIT 1 AS SF_Campaign_Name,
DAX expression result I got from ChatGPT:
SF_Campaign_Name =
VAR FilteredCampaigns =
FILTER(
'Campaign',
'Campaign'[StartDate] <= 'Opportunity'[CreatedDate] &&
'Campaign'[`Campaign ID.Campaign ID`] IN
SELECTCOLUMNS(
FILTER(
'Campaign',
'CampaignMember'[Contact.AccountId] = 'Opportunity'[AccountId]
),
"CampaignID", 'CampaignMember'[`Campaign ID.Campaign ID`]
)
)
VAR LatestCampaign =
TOPN(
1,
FilteredCampaigns,
'Campaign'[StartDate],
DESC
)
RETURN
MAXX(LatestCampaign, 'Campaign'[StartDate])
kimheinemann Happy to have been able to help you! If you need anything else, feel free to ask. In the meantime, accept my answer as the solution to help other users as well!
BBF
5 Replies
- BeaBF
Super User
Hey BeaBF thanks so much for the fast reply. WHile I cant share a sample data set, I can give you a screen shot of the errors I get in DAX.
Error Message:
A single value for column 'CreatedDate' in table 'Opportunity' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
Screen shot attached.
- BeaBF
kimheinemann try with:
SF_Campaign_Name =
VAR OpportunityCreatedDate =
MAX('Opportunity'[CreatedDate])
VAR FilteredCampaigns =
FILTER(
'Campaign',
'Campaign'[StartDate] <= OpportunityCreatedDate &&
'Campaign'[Campaign ID] IN
SELECTCOLUMNS(
FILTER(
'CampaignMember',
'CampaignMember'[Contact.AccountId] = MAX('Opportunity'[AccountId])
),
"CampaignID", 'CampaignMember'[Campaign ID]
)
)VAR LatestCampaign =
TOPN(
1,
FilteredCampaigns,
'Campaign'[StartDate],
DESC
)RETURN
MAXX(LatestCampaign, 'Campaign'[Campaign Name])BBF
Amazing thank you sooo much! That is the first time I did not get any error messages when creating the meaure. I am currenlty only getting the "other' value returned but I feel like I am going in the right direction. You are truly a data wizard thank you again!
