Alternative to LOOKUPVALUE

[etalon]

I have got this to work, but I really don't like how I have done it, and was wondering if there was a better way?

I have two tables:

1. TankID: contains the unique tank ID plus related information on the tank
2. FuelLevels: contains three columns-  TankID, Date/Time, FuelLevel

I have a new calculated column in tankID, whos job it is to get the latest fuel level for that tank from FuelLevels.

I have a relationship set up on TankID, (one to many, single way filtering)

calculated column is: 

 = lookupvalue(FuelLevels[Value],FuelLevels[Date/Time],calculate(max(FuelLevels[Date/Time])),FuelLevels[TankID],[TankID])

Logic: searching the FuelLevels table:

• search column: date/time. Find the latest date/time, and search for this value

I dont really like this part, it seems messy, but it works.

• Search tankID column in FuelLevels, search for the current TankID.

I really dont like this, as it seems superflous. (this should be done by relationships?)

Can someone tell me how to do this better!!

Many thanks

*edit*

I want to use something like this:

 = FILTER(FuelLevels,MAX([Date/Time])), but this obviously returns a table with multiple columns, I dont know how to just pull out just the fuel levels one (and ensure I only get one result)

[v-lili6-msft · Accepted solution]

hi, @etalon 

For your requirement, you could try this formula:

Column 2 = var _maxdatetime=CALCULATE(MAX(FuelLevels[Date/Time])) return
CALCULATE(SUM(FuelLevels[Value]),FuelLevels[Date/Time]=_maxdatetime)

Best Regards,

Lin