Find ID in another table based on filters

[Anonymous]

I have an activity log and a log of performance visits.  I am essentially trying to assign each activity in the log a visit based on the filters below.  Specifically, I am trying to find the performance visit that occured most recently before the activity.  I managed to calculate the date and have that in a column but need the ID for the associated visit as well in another column.  The equation for the date is:

Last PV Before Activity = CALCULATE(
    MAX('History of Performance Visits'[Date of Visit]),
    FILTER('History of Performance Visits','History of Performance Visits'[Date of Visit] < Activity[Date]),
    FILTER('History of Performance Visits', 'History of Performance Visits'[aM Dealer Number] = Activity[aM Dealer Number])
)

This is what I have so far to find the ID:

Last PV ID Before Activity = 
    VAR datematch = FILTER('History of Performance Visits','History of Performance Visits'[Date of Visit] < Activity[Date])
    VAR dealermatch = FILTER('History of Performance Visits', 'History of Performance Visits'[aM Dealer Number] = Activity[aM Dealer Number])
    VAR maxdate = MAX('History of Performance Visits'[Date of Visit])
    
RETURN LOOKUPVALUE('History of Performance Visits'[PV ID],    
        'History of Performance Visits'[Date of Visit], maxdate,
        'History of Performance Visits'[aM Dealer Number],dealermatch,
        'History of Performance Visits'[Date of Visit],datematch)

[Anonymous]

Hi @Anonymous,

Please share us some sample data with expected result so that we can test to coding formula on it.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Activity Table:

Visits Table:

in the rightmost column of the activity table is where I am trying to pull in the answer.  For example, lets say there was a visit (ID: ABC) on 1/1 and another visit (ID: DEF) on 1/5.  There was activity on 1/2, 1/3, and 1/4, all 3 of those activities should have "ABC" in the right most column.  I am able to calc the date  using the first equation above but can't retrieve the ID.

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can try to use following formula if it works:

Last PV ID =
VAR _filtered =
    FILTER (
        ALL ( Visits ),
        [Date of Visit] < EARLIER ( Activity[Date] )
            && Visits[aM Dealer Number] = EARLIER ( Activity[aM Dealer Number] )
    )
VAR _lastdate =
    MAXX ( _filtered, [Date of Visit] )
RETURN
    MAXX ( FILTER ( _filtered, [Date of Visit] = _lastdate ), [PV ID] )

Regards,

Xiaoxin Sheng