Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Milagros
Helper I
Helper I

Table Function, Filter table for values NOT in another table

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
4 REPLIES 4
Anonymous
Not applicable

Hi @Milagros ,

 

Try EXCEPT() function.

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

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

 1.PNG

2.PNG

3.PNG

 

Best Regards,

Jay

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
Not applicable

@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)

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?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.