Help with SELECT() & IN() expression

[Jon_S]

Hi everyone

Got a quick question:

I’m creating a select expression for a report… and i’ve managed to make it work ALMOST to the point of perfection but i’ve stumbled on a seemingly simple step.

Here’s my expression:

SELECT(
JOB_MAIN_AREAS[MAIN_AREA_UID],
AND(
	IN([Foreman],[Related CHECKPOINT_MASTERs][Job Original Foreman]),
	[Related CHECKPOINT_MASTERs][CreatedDate] >= [FROM],
	[Related CHECKPOINT_MASTERs][CreatedDate] <= [TO],
))

My original expression was only included the IN() expression and that was great because i did get a list of areas where the selected [Foreman] = [Job Original Foreman].

And now i simply want to limit this list of areas even more but not just by matching the Forman columns but also by comparing the dates…

Any ideas?

[Steve]

Should the CreatedDate values be those from the matching Job Original Foreman rows?

[Jon_S]

Yes @Steve exactly right!

The [Foreman] match is a prerequisite…

[Jon_S]

I guess i could do it in two VCs or something like that

Create a VC#1 with all the Main Areas matching the foreman… and then a VC#2 that filters out based on the date range… and use that as the basis for the report… but seems like a bit of a clunky solution. Not very elegant. Amateurish. Inefficient.

[Steve]

This is for the report’s <<Start>> expression?

[Jon_S]

Yes exactly… preferably i would like to build the list in the template and not use any VCs.

[Steve · Accepted solution]

Maaaaybe this???

FILTER(
  "JOB_MAIN_AREAS",
  ISNOTBLANK(
    SELECT(
      [Related CHECKPOINT_MASTERs][_ROWNUMBER],
      AND(
        [Job Original Foreman] = [Foreman]),
        [CreatedDate] >= [FROM],
        [CreatedDate] <= [TO]
      )
    )
  )
)

[Jon_S]

@Steve

As always

(there’s a rogue parentheses in there - removed)

I had a quick follow up question.

So this now generates a list of Ref values. Is there a way to Tap into them? What if i needed to sum up a list of numeric values each of which is stored in each of these refs?

Sorry for the basic questions…

EDIT: Actually managed to do it myself thanks for the massive help anyway!