SELECT elements from column that are NOT in another column

I need to present a drop down menu to a user to select records that need processing, identified by the fact that the key parameter value is NOT contained in another table of a list of processed records. I am using the SELECT() function to compare as that seems most logical, however it only ever seems to return the full [unfiltered] list of records from the original column. This is my expression:

SORT(
SELECT(
Departure[TachoStart], ( Departure[TachoStart] <> Arrival[TachoStart] ), TRUE
), TRUE
)

I want to return records from Departure[TachoStart] whereby Departure[TachoStart] values are NOT matched with those of Arrival[TachoStart], as a match means the record [i.e. journey] is completed, using [TachoStart] as the key parameter that links both tables. I then want the records sorted in descending order.
Not sure how important it is, but Arrival[TachoStart] is defined as type text, as it is derived from a drop down menu, using data validation from Departure[TachoStart], that allows the user to choose the departure record to which the arrival info is to be appended, whereas Departure[TachoStart] is a decimal [as they’re all just numbers!!]. However, the expression assistant seems happy with this mismatch in data type, it just doesn’t give me the expected output.

I tried NUMBER() but it didn’t like that
( Departure[TachoStart] <> NUMBER( Arrival[TachoStart] )

whereas LIST() doesn’t appear to do make any difference
( Departure[TachoStart] <> LIST( Arrival[TachoStart] )

I also tried FILTER() instead of SELECT(), but no difference, it still returns the full list
SORT(
FILTER(
“Departure”, Departure[HOBBS2Start] <> LIST( Arrival[HOBBS2Start])
), TRUE
)

help greatly appreciated. Thanks

You would want to use the functions NOT() and IN() like so:

SORT(
      SELECT(Departure[TachoStart],  
             NOT(IN([TachoStart], SELECT(Arrival[TachoStart], TRUE)))),
      TRUE
)

This portion:

SELECT(Arrival[TachoStart], TRUE)

…selects ALL [TachoStart] values from the table Arrival. If there is some criteria that can shorten this list then replace TRUE with that criteria.

Hope this helps!!

1 Like

Or maybe try List Subtraction (channeling my inner Steve here :smiley: ) :

Departure[TachoStart] - Arrival[TachoStart]

Also please review this article, as you don’t seem to have fully comprehended how to use SELECT()

4 Likes

Yes. I need to be more in the habit of using it.

So the complete expression with the shorthand would be:

SORT(Departure[TachoStart] - Arrival[TachoStart], TRUE)

@Marc_Dillon - A question. Should I always be able to use Arrival[TachoStart] in place of SELECT(Arrival[TachoStart], TRUE)??

I ask because it seems like frequently the shortcut doesn’t work for me and I end up using the longer form anyway.

1 Like

Yes, absolutely.

That would be very odd, if you see it again please bring that situation up in the community.

1 Like

Probably just me doing something wrong

so the answer was much simpler than I thought. Many thanks

1 Like