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 Id Person_Id Campaign_Id Date c...
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