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

19 REPLIES 19
YosefF
New Member

One simple solution would be to create a new data table for the list of values you need. Link it to your main data table, and then you can add it as a filter in your visuals.

Robbro
New Member

This is insane not to offer this basic feature... I simply use Qlik products which has this ability built in. 

pedropaixao
New Member

Hey there!

 

I found a visual on market place called "Filter By List", it is a very simple visual that does exactly want we are try to solve here.

 

BR,

Pedro Paixão

Could you please send the link?

This is the visual - worked perfectly for me.

 

https://blog.pragmaticworks.com/power-bi-custom-visuals-filter-by-list

 

 

Not sure why we can't do this natively....

This visual seems to be discontinued by pragmatic works, has anybody found a replacement? I spoke to their support and they have no information on why its not longer available and dont plan to replace it.

Shelley
Continued Contributor
Continued Contributor

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

Shelley
Continued Contributor
Continued Contributor

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.

Anonymous
Not applicable

It doesn't work now.

Shelley
Continued Contributor
Continued Contributor

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

Anonymous
Not applicable

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

Shelley
Continued Contributor
Continued Contributor

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"

 

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.


@ 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!:
Mastering Power BI 2nd Edition

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.

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


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors