Forum Discussion
pass the slicers/parameter values into sql query (BIG QUERY GCP)
Hi thimmaraju ,
Currently, the behavior you’re seeking isn’t supported with the BigQuery connector. Power BI does not allow slicer selections to be incorporated directly into custom SQL statements for BigQuery, nor does it support dynamic M query parameters with this connector. As a result, examples that apply to KQL or other Azure-based sources are not applicable in this scenario.
With BigQuery, the recommended approach is to load data as usual and let Power BI manage filtering. If you utilize DirectQuery, selections made in the report, such as Year or Month, are automatically reflected in the SQL queries that Power BI sends to BigQuery, providing the intended filtering effect without manual parameter integration. In Import mode, however, slicers do not affect the SQL at refresh because parameters are not updated dynamically.
While this may seem restrictive if you wish to directly control the SQL, it reflects the current capabilities of the connector. At present, BigQuery does not allow binding slicer values into native queries using dynamic M parameters, so using DirectQuery and allowing Power BI to handle filter folding is the most effective solution.
Thank you.
Super UserHi thimmaraju
It is possible to pass slicer values as query parameters if you're using direct query via dynamic M query parameters -
Dynamic M query parameters in Power BI Desktop
Note: This isn't possible if you're importing the data source.
Hi @danextian
i have seen your artical but its supporting for KQL which is present in AZURE, but i require it which can support for big query gcp. could you please let me know on big query.
Hi thimmaraju ,
Currently, the behavior you’re seeking isn’t supported with the BigQuery connector. Power BI does not allow slicer selections to be incorporated directly into custom SQL statements for BigQuery, nor does it support dynamic M query parameters with this connector. As a result, examples that apply to KQL or other Azure-based sources are not applicable in this scenario.
With BigQuery, the recommended approach is to load data as usual and let Power BI manage filtering. If you utilize DirectQuery, selections made in the report, such as Year or Month, are automatically reflected in the SQL queries that Power BI sends to BigQuery, providing the intended filtering effect without manual parameter integration. In Import mode, however, slicers do not affect the SQL at refresh because parameters are not updated dynamically.
While this may seem restrictive if you wish to directly control the SQL, it reflects the current capabilities of the connector. At present, BigQuery does not allow binding slicer values into native queries using dynamic M parameters, so using DirectQuery and allowing Power BI to handle filter folding is the most effective solution.
Thank you.Hi thimmaraju ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.
Hi thimmaraju ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.