Most Recent Value from Related Table

[rcopenhagen]

Hi folks,

I have two tables which I am trying to map some information across.

The first table is campaign members from Salesforce:

CampaignMember

Id	Person_Id	Campaign_Id	Date
cm1	p1	c1	3/1/18
cm2	p2	c1	4/1/18
cm3	p1	c2	6/1/18

The second table is a custom object which captures a moment in time related to person records

CustomObject

Id	Person_Id	Date
co1	p1	3/5/2018
co2	p1	9/15/2018
co3	p2	6/2/2018
co4	p2	7/19/2018

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.

Thanks,

Rich

[verscev]

Hello Rich,

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.

Regards,

Evi

[rcopenhagen]

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.

Thanks,

Rich

[verscev]

Hello Rich,

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.

Evi