Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
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
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...
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
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.
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??
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/
I don't believe this is possible