Forum Discussion
Lookup in measures with context filter
Hi,
I would like to use to include a lookup function but with context filter, which does not seem possible with "lookupvalue".
Let me explain, I have a (big) table called "truckload" with a column of:
- IDs,
- timestamp (of events)
- data (when event occurs)
| IDs | timestamp | value |
| 1B4E60A | 2021-09-28T09:00:04 | 43 |
| 2B4E60B | 2021-09-28T09:00:08 | 42 |
| 1B4E60A | 2021-09-28T09:00:26 | 4 |
When I use a slicer of time to visualize information (a selection of time between dates), I have a measure to get the lastest time event of each ID in the defined date/time window:
| ID | latesttime |
| 1B4E60A | 2021-09-28T09:00:26 |
| 2B4E60B | 2021-09-28T09:00:08 |
But then I would like the "value" corresponding to this "latesttime" event in my table to have:
| ID | latesttime | latestvalue |
I tried to use lookup functions but I cannot succeed to keep the context filter of the ID.
For exemple, the following does not work because I have identical timestamps for the IDs and so this lookup function returns often -50 (multiple values are found, the IDs are not separated in the search):
Note that I have unique timestamps for each ID.
So how can I get the value corresponding to this "latestime" using a measure?
Thanks!
Regards,
Renardo
Anonymous , I think you should try measure like
lastesttime = CALCULATE(MAX(truckload[timestamp]),ALLEXCEPT(truckload,truckload[ID]))
lastest value = CALCULATE(lastnonblankvalue(truckload[timestamp], sum(truckload[value])),ALLEXCEPT(truckload,truckload[ID]))
2 Replies
- amitchandak
Super User
Anonymous , I think you should try measure like
lastesttime = CALCULATE(MAX(truckload[timestamp]),ALLEXCEPT(truckload,truckload[ID]))
lastest value = CALCULATE(lastnonblankvalue(truckload[timestamp], sum(truckload[value])),ALLEXCEPT(truckload,truckload[ID]))