Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rcopenhagen
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

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors