Forum Discussion

Koritala's avatar
Koritala
Icon for Post Patron rankPost Patron
1 year ago
Solved

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...
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    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's avatar
Nasif_Azam
Icon for Super User rankSuper User
1 year ago

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:

 

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