Report with related table using a Select() function

[Kara_O_Neill]

In the process of creating a report to run at month’s end of employee mileage. The app is set up with a 1:M type of relationship. Employee table with a Reference column to the mileage table. I have a virtual column in the Employee table that works perfectly for what I want and that is pulling only those mileage records within the month using this select function:

Select(Mileage[Miles], And([Employee] = [_THISROW].[Employee], [Date] <=EOMONTH(TODAY(), 0),[Date] >=EOMONTH(TODAY(), -1)))

I am able to SUM that to get a total of miles per month per employee.

My report is nearly 100% except that for the life of me I can’t get an expression to work to see the breakdown in the table of only the records pertaining to the current month. Everything I try using the [Related Mileages] reference column has been a bust also with the Select function at the top.

[Marc_Dillon]

What if you took your SELECT expression:

Kara_O_Neill:

Select(Mileage[Miles], And([Employee] = [_THISROW].[Employee], [Date] <=EOMONTH(TODAY(), 0),[Date] >=EOMONTH(TODAY(), -1)))

And used that inside of <<START:…
But replace SELECT(Mileage[Miles] with FILTER(Mileage

Or in other words, I don’t think that [Miles] in the key-column for the Mileage table, and START expressions require a list of key values.

[Kara_O_Neill]

I have definitely tried a few variations of that. The key column in the mileage table is a [Record ID] since it is kind of the only unique thing about that table.

Which “Start” are you referring to the one at the top of the report or embedded into the mileage table?

[Marc_Dillon]

The Mileage Start.

You could also do

<<START: SELECT( [Related Mileages][Record ID] , And( [Date] <=EOMONTH(TODAY(), 0),[Date] >=EOMONTH(TODAY(), -1)) >>

[Kara_O_Neill]

Marc_Dillon:

[Related Mileages][Record ID]

Let me give that Select a go. The filter actually worked, except it was showing every record regardless of the employee. Thanks for the quick replies btw.

[Marc_Dillon]

Kara_O_Neill:

The filter actually worked, except it was showing every record regardless of the employee.

Probably needed to change [_THISROW] to [_THISROW-1]

[Kara_O_Neill · Accepted solution]

Marc, virtual covid elbow bump.

That key column was the “key” to my undoing. This worked :

<<Start: Select([Related Mileages][Record ID], And([Date] <=EOMONTH(TODAY(), 0),[Date] >=EOMONTH(TODAY(), -1)))>><<[Date]>>

I haven’t designed any super complicated apps for a few years now. Most of mine are now in the “set it and forget it” mode. Very out of practice! Tons of thanks.