How To Filter Data Before loading with Direct Query option using Redshift

[Koritala]

Hi All,
I am using Redshift as a database and consuming the redshift views as source for my semantic model.
I am unable to create a dynamic M-code to read the subset of data based on the slicers in report page.
If anyone of you used the redshift as source, can you share the pbxi file withth proper M-query. it would be helpful for me to understand.

Thanks,

Sri

[V-yubandi-msft]

Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

Thank You.

[V-yubandi-msft]

Hi @Koritala ,
Has your issue been resolved, or are you still experiencing any problems? Your feedback is valuable to the community and can help others facing similar situations. Please let us know if you have any updates.

Thank You.

[V-yubandi-msft]

Hi @Koritala ,

If you've had a chance to review the attached PBIX file. If not, please take a look and let me know if you need any additional information or clarification to proceed.

Best regards,

Yugandhar

[V-yubandi-msft]

Hi @Koritala ,

In addition to lbendlin comments, when using Redshift in DirectQuery mode, query folding is generally possible, but it really depends on how your views are set up and the specific steps you use in Power Query. Even minor transformations can sometimes stop folding from working.

If query folding isn’t occurring, this could explain why slicer based filtering isn’t working as intended. A common workaround is to use Dynamic M Parameters, but keep in mind that slicer values should come from a separate Import mode table, not your DirectQuery source.

We suggest reviewing the Microsoft documentation that  lbendlin shared recently.

Thank you for your continued support @lbendlin, @Nasif_Azam .

Regards,

Yugandhar.

[Koritala]

Hi Yugandar,

Thanks for your response.

One thing I would like to share here, our redshift views are simple select statement with basic filter conditions on date columns and boolean columns.

Onemore observation is when we create a dynamic query parameters, for date slicer, we need to be creared to different queries one for start and another for end date that to both date slicers will be in drop down format. But in my report user expecting the slicer range where date columns default support the range slicer.

Any suggestions please?

Regards,

Sri

[V-yubandi-msft]

Hi @Koritala ,

Dynamic M Parameters in Power BI must be linked to columns from an Import mode reference table, and at this time, Power BI only allows single value selection per parameter. This is why you need separate queries for Start and End Dates, and why the slicer appears as a dropdown instead of a range slider.

As a workaround you can try

1. Create two Import mode date tables one for Start Date and one for End Date

2. Bind each table to a Dynamic M Parameter in Power Query

3. Use two slicers (Start and End) on the report page to simulate a range selection

4. Filter your Redshift source using these parameters.

Helpful Refernce: Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Hope this helps.

Regards,

Yugandhar.

[Koritala]

Hi yubandi,

Could you please share reference pbxi file which has proper code. If possible, share file with redshift database table.

[V-yubandi-msft · Accepted solution]

Sorry for the delay in responding. 

You can use parameters in Power BI to filter the data as shown in the attachment. If that approach doesn’t work, an alternative workaround would be to create a dedicated reporting table in Redshift and use it as the source for your view. Then use view as a source in  Power BI model in Direct Query mode. You can populate this reporting table by writing a stored procedure that refreshes the data daily (or as per your requirements) and schedule it accordingly. Since the model is connected in Direct Query mode, the report will always reflect whatever data is available in the reporting table, and you can still apply additional filtering within Power BI using page level slicers.

Thanks.

[Nasif_Azam]

Hey @Koritala ,

In DirectQuery mode, M queries are executed once during schema load, and slicer interactions affect the DAX queries generated at runtime not the Power Query layer. To filter data dynamically based on slicers:

1. Ensure your Redshift views are optimized and contain only necessary columns and rows.

2. Build slicers in Power BI using dimensions like Year, Product, etc.

3. Power BI will automatically push DAX queries to Redshift at runtime, filtered based on slicer selections.

For Detailed Information:

• DirectQuery in Power BI – Microsoft Docs

• Optimize DirectQuery models – Microsoft Docs

• Amazon Redshift as a Data Source – Microsoft Docs

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

[lbendlin]

Does the data source show as Direct Query in the data model view?  Are you getting error messages that your queries don't fold?

[Koritala]

Hi lbendlin,

Thanks for your response.
I am connecting through Direct Query Mode as my data in views are huge.

My views are having only required columns. I am not able to get the Query folding. I am struck with the approach to dynamically filter (On the fly based on my report slicers, query need to redshift and fectch the required data to improve my report performance.) 

Please suggest the better approach. if you can share the M code with one or two dynamic filter columns, it will be helpful for me.

Thanks,

Sri

Preview

[lbendlin]

 I am not able to get the Query folding.

That's a showstopper.  Can you elaborate why you are not getting folding?

[Koritala]

As Far as I know Redshift database doesn't support Query Folding or Incremental Refresh functionality. correct me if I am wrong.

Thanks,

Sri

[lbendlin]

Power Query Amazon Redshift connector - Power Query | Microsoft Learn

[Koritala]

Hi lbendlin,

I have gone through your url but none of the place not talked about query folding. Apart from that, we followed the same approach in our semantic model and service. 

[lbendlin]

Since Redshift offers Direct Query mode it must support a SQL style language, and it must support folding.

Maybe you are looking for this feature?

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

NOTE:  The column you use for the slicer ( and bind to the parameter ) must come from an import mode reference table.

[Koritala]

Hi lbendlin,

I have gone through the video and onthing is that same functions may not work. for redshift it may be different functions need to use.

Regards,

Sri

Preview

[lbendlin]

NOTE:  The date column you use for the slicer ( and bind to the parameter ) must come from an import mode calendar reference table.  Instead of a date range your Power Query must be able to accept a list of values  ( the IN statement in SQL ).