Expression returning wrong rows - what am I doing wrong?

[Alex_Baker]

Hi, so I have this expression:

sum(select(Materials[Quantity], and([Material] = [_THISROW].[Material], [Batch]=any(Open_Batches[Batch ID])))) - any(select(Leftovers[Quantity], and([Material] = [_THISROW].[Material], [Batch] = any(Open_Batches[Batch ID]).[PreviousBatch])))

It runs from a behaviour iterating through each material type, and calculates the total used for each material type for the batch by summing the quantities of materials in another table (these rows belong to the batch), and subtracting the amount the batch started with (=the leftovers from the previous batch, also rows in another table belonging to the batch). It mostly works fine, the only problem is that the second statement is returning leftovers belonging to the current batch rather than the previous one, which is what I want.

Open_Batches is a slice. It only ever contains one row.
[PreviousBatch] is a ref to another batch in Batches.
Leftovers.[Batch] is also a reference to a batch (the one it belongs to).

I really can’t see why this isn’t behaving as expected, so any help would be appreciated.

Thanks!

[Steve · Accepted solution]

This is a problem: any(Open_Batches[Batch ID]).[PreviousBatch]. You cannot dereference an expression.

Try replacing [Batch] = any(Open_Batches[Batch ID]).[PreviousBatch] with this:

IN(
  [Batch],
  SELECT(
    Batches[PreviousBatch],
    IN([Batch ID], Open_Batches[Batch ID])
  )
)

[Alex_Baker]

Ah that works as expected, thank you. I didn’t realise you couldn’t dereference an expression - maybe that should throw an exception?

[Steve]

Alex_Baker:

maybe that should throw an exception?

It should, yes. My impression–based on responses I’ve gotten to my own similar queries in the past–is that the library used to parse expressions has limitations that make catching errors like this difficult.