undefined

[Koritala]

Hi,

Can anyone share the M-code to pass the dynamic parameters to filter the data using redshift custom SQL .

Let's say I want to pass start date and end date, also anyone dimension(example, Country with Multiselect option in slicer)

Also, my dates selection shold be through date picker calendar only not drop down list.

Thanks,

Srinivas.

[v-tsaipranay]

Hi @Koritala ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @Koritala ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Also thank you @rohit1991  for your insights.

Thank you.

[rohit1991]

Hi @Koritala 

• Dates: Yes. Create Power Query parameters StartDate/EndDate and pass them in Value.NativeQuery(...). Works in DirectQuery only if the step folds; otherwise it’s refresh-time only.

• Country multi-select from a slicer >> SQL IN (...): Not supported. Power BI parameters are single-value and slicers can’t push a dynamic list into custom SQL.

1. Filter in the model: load/stage the table and let slicers filter in DAX (no SQL push-down).

2. Import mode: use a parameter table + join to simulate multi-select.

3. True push-down: call a stored procedure and pass a CSV list (parse in Redshift, e.g., SPLIT_TO_TABLE) or embed/Power Apps to collect filters and trigger the query.

[v-tsaipranay]

Hi @Koritala ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Also thank you @Poojara_D12  for your insights.

Thank you.

[Poojara_D12 · Accepted solution]

Hi @Koritala 

To pass dynamic parameters to a Redshift custom SQL query in Power BI using M code, you can define query parameters in Power BI and refer to them inside your M (Power Query) code. For your scenario—where you want to pass a start date, end date, and a multi-select dimension like Country—you first create three parameters in Power BI: two of type Date for the start and end dates (which will allow users to pick dates using a calendar date picker), and one text parameter for Country (though multi-select requires a more advanced trick). In Power Query, you can then embed these parameters inside a custom SQL query using the following M code structure:

let
    StartDate = Date.ToText(ParamStartDate, "yyyy-MM-dd"),
    EndDate = Date.ToText(ParamEndDate, "yyyy-MM-dd"),
    CountryFilter = Text.Combine(List.Transform(ParamCountryList, each "'" & _ & "'"), ","), // This handles multiple selected countries
    Source = Odbc.Query("dsn=YourRedshiftDSN", 
        "SELECT * FROM your_table 
         WHERE order_date BETWEEN '" & StartDate & "' AND '" & EndDate & "' 
         AND country IN (" & CountryFilter & ")")
in
    Source

Here, ParamStartDate and ParamEndDate are Date-type parameters (users will get a calendar picker), and ParamCountryList is a list parameter created via a table or slicer and transformed into a string list for the SQL IN clause. One limitation is that Power BI parameters don’t natively support dynamic multi-select slicers in the report UI—so to achieve a truly dynamic experience (like selecting countries from a slicer in the report), you’d need to switch to using a DirectQuery model or a semantic model filter instead of fixed Power Query parameters, as M code refreshes are evaluated at refresh time, not interactively like DAX.

[v-tsaipranay]

Hi @Koritala 

Thank you for reaching out to Microsoft fabric community forum. Also thankyou @Nasif_Azam for your insights.

In addition to @Nasif_Azam  ,Power BI allows passing static parameters (like Start and End Dates) into custom SQL queries using @@ParameterName syntax. These parameters, when defined as Date/Time, render as calendar pickers. Please have a look in the documentation for your understanding: Use parameters to visualize variables - Power BI | Microsoft Learn

For multi-select slicers such as Country, it's important to note that multi-value slicer inputs cannot be passed directly into Redshift SQL. Instead, the data should be loaded first and slicer filtering applied via DAX, such as using IN VALUES(...), as outlined inFILTER function (DAX) - DAX | Microsoft Learn

Additional guidance on parameterized SQL queries is available Import data from a database using native database query - Power Query | Microsoft Learn

This approach aligns with current Power BI capabilities and Redshift SQL integration.

I hope this gives you an idea, if you need any further assistance, feel free to reach out.

If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

Thankyou.

[Nasif_Azam]

Hey @Koritala ,

To filter data dynamically in Power BI using Amazon Redshift with custom SQL, date pickers, and multi-select slicers (e.g., Country), follow the steps below:

Step 1: Define Parameters in Power BI

Create the parameters for dynamic filtering:

1. Start Date Parameter:

   • Name: StartDate

   • Type: Date/Time

   • Suggested Values: Any value

   • Default value: Leave it blank or choose a reasonable date

2. End Date Parameter:

   • Name: EndDate

   • Type: Date/Time

   • Suggested Values: Any value

These parameters will automatically render as calendar pickers.

Step 2: Write Redshift Custom SQL with Parameters 

Use your parameters in your Redshift query like this:

SELECT *
FROM your_table
WHERE date_column BETWEEN CAST('@@StartDate' AS DATE) AND CAST('@@EndDate' AS DATE)

In Power BI, use @@ParameterName syntax inside the custom SQL window. Power BI will replace it with the actual parameter values.

Step 3: Load Distinct Values for Country

For multi-select in slicer:

1. Load SELECT DISTINCT Country FROM your_table as a separate table called CountryList.

2. Set up a slicer from this table (CountryList[Country]) and make sure it’s a multi-select slicer.

Step 4: Use DAX Filter in Report (Post-import Filter for Country)

Since Redshift custom SQL can't directly use multi-select slicer values, apply the country filter using DAX in a calculated table or in a visual-level filter:

FilteredData =
FILTER (
    your_table,
    your_table[Country] IN VALUES (CountryList[Country])
)

Or create a measure to show filtered results:

Total Sales :=
CALCULATE (
    SUM (your_table[SalesAmount]),
    FILTER (
        your_table,
        your_table[Country] IN VALUES (CountryList[Country]) &&
        your_table[Date] >= StartDate &&
        your_table[Date] <= EndDate
    )
)

Things to Remember

• Only static parameters can be passed to Redshift custom SQL. Multi-select slicer values (like Country) must be filtered using DAX after loading the base data.

• Using DirectQuery with Redshift + parameters might not fully support dynamic multi-select.

• To handle everything in SQL, consider implementing a stored procedure or view in Redshift and import that into Power BI.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam