How can I filter by a list of values?

[vmazz77]

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.

[NorwaySasquatch]

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]

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

[Shelley]

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]

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"

[NorwaySasquatch]

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

[Greg_Deckler]

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.

[wsethpollock]

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.

[cathoms]

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.

[vmazz77]

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.

[Greg_Deckler]

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??

[Shelley]

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

[konstantinos]

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/

[andre]

I don't believe this is possible