Breaking Ties in LOOKUPVALUE

[topkatt]

I have a formula that accurately looks up the value of [TYPE] for an [ID] where Date = [Last Event Date]:

Last Type = (LOOKUPVALUE('Data'[TYPE],'Data'[ID],VALUES('Data'[ID]), 'Data'[Date], [Last Event Date]))

My question is how to alter this formula for cases where there are multiple rows with the same ID and Date but different values of TYPE?

Thanks for your help.

[TomMartens]

Hey,

not sure if you want to create a calculated column or a measure, but basically you can try something like this (for a measure):

var currentID = CALCULATE(FIRSTNONBLANK('Data'[ID],1))
return
CALCULATE(
FIRSTNONBLANK('Data'[TYPE],1)
,FILTER(ALL('Data'[ID],'Data'[Date])
,'Data'[ID] = currentID && 'Data'[Date] = [Last Event Date]
)
)

Hopefully this is what you are looking for

Regards,

Tom