Forum Discussion

rcopenhagen's avatar
rcopenhagen
Frequent Visitor
7 years ago

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

  • verscev's avatar
    verscev
    Frequent Visitor
    7 years ago

    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's avatar
      rcopenhagen
      Frequent Visitor
      7 years ago

      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's avatar
        verscev
        Frequent Visitor
        7 years ago

        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