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

0 6 831
6 REPLIES 6

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!!

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

Departure[TachoStart] - Arrival[TachoStart]

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

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.

Yes, absolutely.

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

Probably just me doing something wrong

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

Top Labels in this Space