Dynamic Query Parameters In M code

[Koritala]

Hi, 

I have a view with different columns in redshift db. I want to filter out the view data at the redshift db level.

I need to pass dynamic filters with multiple values passing like, In category parameter, I want to select electronics, furniture like that.

and in another dynamic parameter Region, East,West like that.

I need help to write dynamic Mcode to pass 2 dynamic parameters. when user select values in slicers in report page, accordingly filters should push to redshift db and filter the data at db and same will show in visual level.

Approciate if any one can quickly give the response.

Thanks,

Srini

[V-yubandi-msft]

Hi @Koritala ,

Everything is clear now. Please let us know if you need any additional information.

Thanks.

[Koritala]

Hi V-yubandi,

with redshift db query folding is not enabling.

If you can proven practically, I would appriciate.

Thanks,

Srini.

[V-yubandi-msft]

Hi @Koritala ,

You are correct query folding is not occurring in your scenario, which is expected. With Redshift, folding typically does not happen when the source is a view or when there are transformations like joins, CASE statements, or casts, so filters are not pushed to the database.

To clarify, Power BI does not support passing multi select slicer values into Power Query  at runtime, so this method will not work with any database.

If you need database level filtering, the options are

1. Use DirectQuery

2. Connect to base tables or materialized views

3. Allow slicers to apply filters automatically when folding is possible

Please let us know if you need assistance with validating folding or modifying the model.

Regards,

Yugandhar

[Koritala]

Hi v-yubandi,

Thanks for your response.

I just want to let you know that query folding is not happening with tables also with redshift. 
Reg.to filters at database, using dynamic  query parameters with dynamic query is the option.

Thanks,

Srinivas.

[V-yubandi-msft]

Hi @Koritala ,

Yes  this is the same point I mentioned earlier, and there are no additional supported options beyond those already outlined.

Dynamic query parameters are evaluated only at refresh time and cannot be driven by slicer selections, so they do not enable database level filtering during report interaction.

Thank you for the discussion. Please let us know if you need any further clarification or assistance.

Regards,
Yugandhar.

[V-yubandi-msft]

Hi @Koritala ,

If you get a chance, please review the responses shared by @lbendlin  and @cengizhanarslan , as they address the points exactly. Let us know if you need any additional details.

Thanks.

[lbendlin]

You can bind a parameter to exactly one column.  So in your case you would need two parameters.

A bound column can return zero, one or multiple values. Your Power Query needs to probe the returned value and modify the SQL accordingly.  One fancy way is to treat all input as lists, even single values.

[cengizhanarslan]

You cannot dynamically pass multi-select slicer values into Power Query (M) and have them pushed to Redshift at runtime. This is a hard limitation of Power BI’s architecture.

Alternative Option: Use DirectQuery and let DAX handle filtering

• Model in DirectQuery

• Slicers filter dimensions (Category, Region)

• Power BI generates SQL with WHERE … IN (…)

• Filters are pushed down to Redshift automatically (query folding)

This requires no M-code parameters at all.

If your Redshift source supports folding (it does), this is the clean and supported solution.