filter on multiple values using parameter, in the query editor

[Renaud]

Hello all,

I have both some referential and transactional tables refering to my company products.

Each products can be categorized in an "Activity" and "Sub activity" (each activity contains several sub-activity).

Each of my colleague is working on one specific Activity (so, more than 1 sub-activity each).

To reduce loading/refresh time for each of them, I would need to filter the tables on the products of one activity, for each user, and I think the best solution to do that is using a parameter "Activity" with a list of values.

The problem is: each of my referential/transactional tables only refer to sub-activity, without mentioning the parent Activity.

And as far as I know, it is not possible to assign several possible values to one parameter.

What would be then the solution to be able to filter on several sub-activities at the same time, with a parameter?

I was thinking of adding an Acitivity-Subactivity table linking the two fields, and merging this table in the query editor with each each of my ref/transact tables. This way I would have the Activity appearing and be able to filter it using the parameter. But I'm not sure if this will not first load all the lines before filtering, defeating the purpose.

Can anyone help?

thx

Renaud

[TrevLc]

Hi,

I was looking for a solution to this problem, as I was facing it too.

I created a list in a separate query, and then used the Table.SelectRows function, and it folded.

Here is a blog I found about it.

Filtering a Table to a List of Values in Power Query or Power BI - Excel UnpluggedExcel Unplugged

Hope it helps

[Harold_Pajuelo]

I only created a query with de values that I want to filter, then I merged with the principal table for the colum I want to filter, after in the new column I filtered values distinct to blank

[sandermatthijs]

The problem with Merge solution is that it does not FOLD to the server; the way that the Power Query engine pushes the heavy-lifting of a query back to a data source/server (search for Chris Webb's BI Blog 'How To Tell Whether Query Folding Is Taking Place When Importing Data From Analysis Services In Power BI And Excel Power Query'). This means that powerbi will do (as far as I understand it):
1) Look a the statements, and if table is more that something like 4000 rows, say "oh dear I can't figure this out, let me download it first".
2) download the whole table (problem is; that could be a few milion rows.
3) Look at the merge statement and then apply the 'matching' (joining if you will).
Result of merge: 15 mins waiting (or longer obviously) and millions of rows downloaded, but only keeping some of them (the ones you merged with) after mashup data is dumped and loaded to the model.
 
The frustrating thing is: SQL can do this easily with an IN statement. Mashup seems to struggle 😞

[v-yuezhe-msft]

Hi @Renaud,

Could you please relevant screenshots of your scenario?

You can review the detailed steps about how to use parameter in the following blogs.

http://biinsight.com/power-bi-desktop-query-parameters-part-3-list-output/
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/ 

Thanks,
Lydia Zhang

[Renaud]

Hi @v-yuezhe-msftLydia,

thanks for your reply. I will try to include the screenshots if really necessary but let me try to explain further.

Yes there is of course for each parameter multiple values to choose from, but still only one current value that you can assign to the parameter at one time.

The first link that you provide is a very good example of what I would like to achieve

They filter the FactInternetSales table by using a parameter to filter the Product Category (choosing 1 value only), that will itself filter the Product Sub Category and cascade down to the fact tables.

However, they do that on the report, after full import of the dim & fact tables. My goal, in using a parameter, would be to do it during the import (in the query editor). I have a subcategory information directly in the fact table (on top of the product code), which allows to do some filtering. If I could do this filtering, this would allow to load only the filtered data and improve the loading/refresh time.

Do you see a way to achieve this?

[sandermatthijs]

I have exactly the same question : how to use the WHOLE list as the filter?

I understand how to use the Paremeter list to filter on ONE value, but I'd like to use it to overcome a query Folding issue (filters and transformations are done on Server rather your laptop, so you odn't need to download 8M rows first..), by applying something like a "where IN" SQL statement to the filter/query.

Maybe the Paremeter is not the way to go here? The need to limit a set of results based on "return only the following few items from the big database" seems like a fairly basic requirement; I do it in SQL all the time using [where xx IN ('abc', 'abd', etc)].

[veganEarth]

Did you find a solution to this? 

It seems odd that you can provided a list of values to use as a parameter but you can only select (manually) one at a time and not load up all of the list.

Please tell me I am missing something obvious on how to use the "List of values" option.

Thanks

[Renaud]

Hello,

no i have not found any solution to date.