Forum Discussion

Koritala's avatar
Koritala
Post Patron
7 months ago

Dynamic Query Parameters In M code

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

 

 

Need Help

8 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 months ago

    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.

  • lbendlin's avatar
    lbendlin
    Super User
    7 months ago

    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.

    • Koritala's avatar
      Koritala
      Post Patron
      6 months ago

      Hi V-yubandi,

      with redshift db query folding is not enabling.

      If you can proven practically, I would appriciate.

      Thanks,

      Srini.

       

      • V-yubandi-msft's avatar
        V-yubandi-msft
        Community Support
        6 months ago

        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