Forum Discussion
Post PatronHow To Filter Data Before loading with Direct Query option using Redshift
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.
Community SupportHi 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.
Post PatronHi 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
-
Create two Import mode date tables one for Start Date and one for End Date
-
Bind each table to a Dynamic M Parameter in Power Query
-
Use two slicers (Start and End) on the report page to simulate a range selection
-
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
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.
-