Forum Discussion
pass the slicers/parameter values into sql query (BIG QUERY GCP)
Need help on this requirement. URGENT.
I have to pass the selected slicer values like year and months as
SelectedYear and SelectedMonth parameters values into BIG QUERY db sql query.
tried with below approach but not working,
created two parameters like
SelectedYear : 2022, 2023,2024,2025,2025
SelectedMonth : 01,02,03,04,05,06,07,08,09,10,11,12
Database: BIG QUERY (GCP)
Connector: Google BigQuery
SELECT
PARSE_DATE('%Y%m%d', event_date) AS event_date,
user_pseudo_id,
user_id,
(
SELECT value.string_value
FROM UNNEST(user_properties)
WHERE key = 'account_0_ConnectionType'
) AS linetype,
platform
FROM
`bigquery-project-8765.analytics_21641234.events_*`
WHERE
_table_suffix BETWEEN
FORMAT_DATE('%Y%m%d', DATE(@SelectedYear, @SelectedMonth, 1))
AND
FORMAT_DATE('%Y%m%d', DATE(@SelectedYear, @SelectedMonth, 1)
+ INTERVAL 1 MONTH - INTERVAL 1 DAY)
AND LOWER(event_name) NOT LIKE '%notification%'
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.
7 Replies
rohit1991Super User
Hii thimmaraju
Passing slicer values into BigQuery SQL is not supported. Load data normally >> use DAX slicers >> Power BI automatically folds the filters back to BigQuery.
Use below link to know more about this :
https://learn.microsoft.com/en-us/power-query/native-query-folding
- Idrissshatila
Hello thimmaraju ,
why dont you read from the datasource as direct query and when you select the year and month, it will query data for the selected period.
check this https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-use-directquery
- danextian
Hi 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.
