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]

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

[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 ).