Forum Discussion
How To Filter Data Before loading with Direct Query option using Redshift
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
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.
18 Replies
Does the data source show as Direct Query in the data model view? Are you getting error messages that your queries don't fold?
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
I am not able to get the Query folding.That's a showstopper. Can you elaborate why you are not getting folding?
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:
-
Ensure your Redshift views are optimized and contain only necessary columns and rows.
-
Build slicers in Power BI using dimensions like Year, Product, etc.
-
Power BI will automatically push DAX queries to Redshift at runtime, filtered based on slicer selections.
For Detailed Information:
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-
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.
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
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.
-
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
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.
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.
