Creating a variable to return max date where title matches list and Emp ID = merged field

building reports
1

Hi there I’m trying to create a variable to add the latest date a specific event was undertaken to add to a report table.

I have one query returning all my current staff and a second query returning all of the event information and these are merged using [Person ID:People]. I’m trying to create a variable to return the max [Start Date:People Learning Event] for a specific training course but over time the course title has changed so could be either “Anti-Fraud Bribery and Corruption”; “Anti-Fraud, Bribery and Corruption” or “Anti-fraud, bribery & corruption”. I have the following but it’s returning “#CONTEXT”.

=Max([Start Date:People Learning Event] In ([Person ID:People]) Where ([Learning Event:People] = “Anti-Fraud Bribery and Corruption” or [Learning Event:People] = “Anti-Fraud, Bribery and Corruption” or [Learning Event:People] = “Anti-fraud, bribery & corruption”)).

I’ve also tried
=If([Learning Event:People] = “Anti-Fraud Bribery and Corruption” Or [Learning Event:People] = “Anti-Fraud, Bribery and Corruption” Or [Learning Event:People] = “Anti-fraud, bribery & corruption”) Then Max([Start Date:People Learning Event])

and
=If([Learning Event:People] = “Anti-Fraud Bribery and Corruption” Or [Learning Event:People] = “Anti-Fraud, Bribery and Corruption” Or [Learning Event:People] = “Anti-fraud, bribery & corruption”) Then Max([Start Date:People Learning Event] In ([Person ID:People]))

But neither of those returns anything.

2

is your variable of type „detail“ ?

3

Hi N8QKTIV No it’s a measure.

4

Resolved it - was so close;

=Max(([Start Date:People Learning Event]) Where ([Learning Event:People] = “Anti-Fraud Bribery and Corruption” Or [Learning Event:People] = “Anti-Fraud, Bribery and Corruption” Or [Learning Event:People] = “Anti-fraud, bribery & corruption”)) In ([Person ID:People])