Help - Turning SQL into DAX to attribute SFDC opportunities to SFDC campaign members

[kimheinemann]

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]

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

[kimheinemann]

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!

[BeaBF · Accepted solution]

@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

[BeaBF]

@kimheinemann Hi! Can you provide sample data and the error?

BBF