I have two tables which I am trying to map some information across.
The first table is campaign members from Salesforce:
The second table is a custom object which captures a moment in time related to person records
I am attempting to create a column on the CustomObject table which would show the most recent CampaignMember Campaign_Id related to the Person_Id on the CustomObject row which is before the Date on the CustomObject row.
My feeble attempt looks something like this:
LastCampaignMember = CALCULATE ( MAX ( 'Campaign Member'[Campaign_Id] ), FILTER ( 'CampaignMember', 'CampaignMember'[Person_Id] = EARLIER ( 'CustomObject'[Person_Id] ) ), FILTER ( 'CampaignMember', 'CampaignMember'[Date] <= EARLIER ( 'CustomObject'[CreatedDate] ) ), FILTER ( 'CampaignMember', 'CampaignMember'[Date] = MAX ( 'CampaignMember'[Date] ) ) )
Any assistance would be greatly appreciated.
I slightly changed the order. I first would select the list of related rows, and then select the max value.
LastCampaignMember = CALCULATE ( VALUES ( CampaignMember[Campaing_Id] ); TOPN ( 1; FILTER ( FILTER ( CampaignMember; CampaignMember[Date] <= CustomObject[Date] ); CampaignMember[Person_Id] = CustomObject[Person_Id] ); CampaignMember[Date]; DESC ) )
I hope it can help you.
Hi @verscev - thanks for helping work this one out!
Power BI is returning this error for the adjusted formula: A table of multiple values was supplied where a single value was expected.
Any idea what's going on there? Your solution seems to be pretty elegant. With larger tables it does cap out my memory, but I reduced the table length for now.
Most probably, the TOPN function returns multiple rows.
In your situation, this can happen when 2 rows are found that have the same CampainMember[Date], which are also the highest value.
You can solve this by replacing the VALUES function (which expects one value), by MAX and/or MIN, depending on your business need.
I hope this helps.