Table Function, Filter table for values NOT in another table

[Milagros]

Hi there, I have two tables. Tabe 1 All reference numbers, Table 2 Some reference numbers. I am trying to filter Table 1 to show only those reference numbers that do not appear in table 2. I tried this but with no luck. After typing the NOT operator, I do not see Table 2 at all.  Is there something wrong with the syntax?  I created a many to many relationship between the Reference number fields in Table 1 and Table 2, but that didn't help.

I want to keep it as simple as possible is there a better way of doing this?

Not in Table 2= FILTER(Table 1,Table 1[referenceNumber],NOT(xxx

Thank you

[v-jayw-msft]

Hi @Milagros ,

Try EXCEPT() function.

https://docs.microsoft.com/en-us/dax/except-function-dax 

Table = EXCEPT('Table a','Table b')

 

Best Regards,

Jay

[Milagros]

Hi Jay, Thanks, I tried that, but got this message "Each table argument of 'EXCEPT' must have the same number of columns." I should mention that my tables have other columns in them and they don't have the same number of columns.

Kind regards, Mila

[Anonymous]

@Milagros Try something like this:

Not in Table 2=var tempFilter=EXCEPT(VALUES(Table 1[referenceNumber]),VALUES(Table 2[referenceNumber]))

return FILTER(Table 1,Table 1[referenceNumber] in tempFilter)

[Milagros]

Hi Marik, Thank you I tried the suggestion but got this message.  "Cannot convert value 'WAL-105221-1' of type Text to type True/False." Maybe Except doesn't play nicely with text values?