Return list based on data NOT in the query

[baBI123]

Hello. 

I am using the "Filter By List by Devascope" visulaization to filter a table of Part Numbers.
When I enter the list into the search box, I want the results to show me all Part Number that are NOT in the list I searched.

Example: 
Enter into search box: 
PN 123
PN 456
PN 789
 
Quering a table that contains: 
PN 123
PN 456

Results (I want ANOTHER table to return the following information): 
PN 789

HOW CAN I DO THIS IN POWER BI? I AM DESPERATE. I NEED HELP.

Does this make sense? 
If not, please ask questions so that I can clarify.

[v-lili6-msft]

hi,@baBI123

     After my research, I'm afraid it couldn't achieve in Power BI for now."PN 789" is not in the data model, 

so it couldn't appear as a result. But you can use this different way as below:

Step1:

create a fact table of Part Numbers that you will need In advance.

IF you want to add new Part Numbers, you can just add them in the data source and then refresh datasets.

like:

PN 123
PN 456
PN 789

...

Step2:

Create the relationship between fact table and data table by Part Numbers

Step3:

Drag field Part Numbers of fact table into "Filter By List by Devscope"

then  drag field Part Numbers of fact table into table visual and drag field Part Numbers of data table into visual

level filter of data table.

set Part Numbers is blank

Result:

here is pbix, please try it.

https://www.dropbox.com/s/bkcq4qmvojtw40a/Return%20list%20based%20on%20data%20NOT%20in%20the%20query...

Best Regards,

Lin

[baBI123]

This is not working for me for some reason.... @v-lili6-msft

Perhaps I should inform you that I had to append my FACT table and DATA table by merging the two tables, keeping only the PN column and then deleting the duplicates within the column. 

I had to do this because when  I was trying to link the DATA table to the FACT table by PN column, there was an error message reading "this can't be done becuase there isn't unique values".... Does this change the way I need to set up my query? 

I really, really appreciate you helping me out.

-K