cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.

12 REPLIES 12
Highlighted
Memorable Member
Memorable Member

Re: How can I filter by a list of values?

I don't believe this is possible

Highlighted
Super User IV
Super User IV

Re: How can I filter by a list of values?

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
New Member

Re: How can I filter by a list of values?

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.

Highlighted
Memorable Member
Memorable Member

Re: How can I filter by a list of values?

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
Highlighted
Super User IV
Super User IV

Re: How can I filter by a list of values?

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Regular Visitor

Re: How can I filter by a list of values?

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"

 

Highlighted
Responsive Resident
Responsive Resident

Re: How can I filter by a list of values?

If you're still looking for this functionality, I've discovered this custom slicer visual. It works. The limitation for me is that it doesn't sync across tabs and it is kind of large, but it comes in handy.

 

https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381413?tab=Overview

 

If this addresses your need, please consider marking this as a solution.

Highlighted
Frequent Visitor

Re: How can I filter by a list of values?

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.

Highlighted
Frequent Visitor

Re: How can I filter by a list of values?

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors