Have 3 datasources, Employees, TBT Documents, TBT Document Reads.
Employees are the employees. TBT Documents are documents they need to read and acknowledge they have done so. TBT Document Reads is the data that stores all the reads by the employees.
Now I can see which employees have been allocated the TBT Documents by the REF ROWS list in the Employee data.
Employee Unique ID is their ID. TBT Document Unique ID is the Document ID. So the TBT Document Read holds the Document Unique ID along with the Employee Unique ID.
I wish to list (REF ROWS) the employees that have not read the TBT Document.
So I need to produce a list of all the employees and - employees contained within the list.
REF_ROWS() is simply a short-hand function for SELECT() to identify rows by matching REF columns between tables.
In this case, you have a CONDITION by which you want to identify a set of rows so you cannot use the REF_ROWS() function.
To create a column that behaves like one of the “Related” (REF_ROWS()) columns, you need to add a Virtual Column and define it similar to that of a “Related” column:
Type is set to List
Element Type is Ref
Table Source - from where you will be selecting rows - i assume Employees
Then assign into the App Formula a SELECT() expression that selects the row Keys from the table source based on your condition. As you pointed out your expression would be like this:
Employees[Employee ID] - TBT Document Read[Employee]
NOTE: These are both ALSO shorthand for a SELECT() function. I made assumptions about column names so will likely need to adjust those.
Have sorted it, but I came up with almost the same. Here is my expression:
FILTER(“TBT Employee”,TRUE)-SELECT(TBT Reads[SQEP List ID], [TBT Ref] = [_THISROW].[Unique ID])