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

Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.

Reply
vmazz77
New Member

How can I filter by a list of values?

We have the need to be able to filter by a list of values.  For example, we have a list of orders and would like to be able to paste the values into a filter for order numbers.  Is this possible in either Power BI or Power BI Desktop? 

 

I understand you can search for a particular value, filter for a contiguous range of values, or check individual values for filtering, but it doesn't seem possible to paste a list of values to filter on.

34 REPLIES 34

Yes, I cannot get Mass Filter to sync across tabs either. So frustrating, not sure if this is developer issue or Microsoft, but ususally lots to blame on Microsoft for disruptions and unknown upgrades causing new bugs.

this is an updated post for this specific issue - see the dev response Re: Issue with Filter by List by Devscope Visual - Microsoft Fabric Community

Anonymous
Not applicable

This is a good visualization addon but it can`t be sync between pages...

This Mass Filter appears to be the same custom visual and is on the app store and does sync across tabs!  Business Apps – Microsoft AppSource  https://appsource.microsoft.com/en-US/product/power-bi-visuals/insiderscoop1611244107840.powerbi_cus...

korneel
Regular Visitor

In my search for a solution for this same problem, I stumbled upon this dormant topic.

 

Luckily, I found a solution:

 

let
Orders = Table.FromRows(
{
{2600, "Beth", "Magical Beans"},
{2601, "Jim", "Fairy Dust"},
{2602, "Sandra", "Ostrich Fairy Duster"},
{2603, "Bill", "Calculator"}
},
{"OrderNo", "Customer", "Product"}), // Your table with orders
OrderNumberSelection = {2601, 2602}, // These are the order numbers we want to use as a filter
#"Filter by list" = Table.SelectRows(Orders, each List.Contains(OrderNumberSelection,[OrderNo]) )
in
#"Filter by list"

 

This works well for one-offs, but doesnt work if an end user wants to go in and upload their own list 

Greg_Deckler
Super User
Super User

I'm going to think about this one some more as that is a pretty odd request. But, you *might* be able to get there using something like this:

 

http://www.daxpatterns.com/parameter-table/

 

 

Basically, come up with a parameter table that you could reference in DAX.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

This is not an odd request. There are many times where someone may want to filter by a list of product numbers, procedure codes, provider id, store id, etc. It may be a very long process to select each individual id, rather than just pasting in a list of filter criteria.

Definitely not an odd request. It would be nice if advanced filtering had "in" or "not in" options where an end user could paste a comma separated list.

Thanks - I appreciate the link and will be interested in anything else you might come up with.  Maybe surprisingly, it's a common use case - an analyst or manager receives a list of orders/products/etc. to research and needs a way to filter the data to get more information about the items on the list.  This is a very easy thing to do in Cognos's ad hoc tool - not so much in the MS world.

In MS world, you would use slicers. I guess what is odd is that you have a list to copy and paste, where does that list come from? Some other system, do they type what they want in Notepad??



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler We need to do this every single day. We typically paste a list of customer IDs or part numbers, for example, usually from Excel. Filter by List from DevScope and Mass Filter both allow paste a list, but neither one syncs across pages now. They both used to do it for free, but then I believe Microsoft changed something and broke them. Also, DevScope moved from free to pay to play; however, I heard from others that even by paying for it, the sync still doesn't work. So, it's not worth us buying it. This is such a common need, I cannot believe Microsoft doesn't have this capability as some sort of standard slicer. 

One way I can think is a powerquery function to filter values based on specific list..only for excel or powerbi desktop - cannot refresh in powerbi service ( functions )..

 

 

A combination of these:

http://blog.crossjoin.co.uk/2014/12/05/power-query-and-function-parameters-of-type-list/

http://blog.crossjoin.co.uk/2015/01/08/multiselect-filtering-and-functions-in-power-query/

 

Konstantinos Ioannou
andre
Memorable Member
Memorable Member

I don't believe this is possible

Helpful resources

Announcements
May PBI 25 Carousel

Power BI Monthly Update - May 2025

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

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

May 2025 Monthly Update

Fabric Community Update - May 2025

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

Top Solution Authors