cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rcopenhagen Frequent Visitor
Frequent Visitor

Most Recent Value from Related Table

Hi folks,

 

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

 

The first table is campaign members from Salesforce:

 

CampaignMember

IdPerson_IdCampaign_IdDate
cm1p1c13/1/18
cm2p2c14/1/18
cm3p1c26/1/18

 

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

 

CustomObject

IdPerson_IdDate
co1p13/5/2018
co2p19/15/2018
co3p26/2/2018
co4p27/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

3 REPLIES 3
verscev Frequent Visitor
Frequent Visitor

Re: Most Recent Value from Related Table

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 Frequent Visitor
Frequent Visitor

Re: Most Recent Value from Related Table

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 Frequent Visitor
Frequent Visitor

Re: Most Recent Value from Related Table

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