Forum Discussion
Return list based on data NOT in the query
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.
2 Replies
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.
Best Regards,
Lin
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
