Forum Discussion
Power BI Paginated Report with BigQuery DirectQuery – Filters not pushed down
Hi m_andel
In your setup, Power BI’s Paginated Report queries a Semantic Model in DirectQuery mode, which in turn connects to BigQuery. However, when DAX filters from the Paginated Report (like date parameters) are applied, they’re often processed at the Semantic Model layer, not pushed down to BigQuery. This means BigQuery returns a large dataset (all 13M rows), and Power BI filters it afterward — causing poor performance. This behavior happens because DAX-to-SQL translation doesn’t always propagate all filters efficiently through DirectQuery, especially for views or complex relationships. To improve performance, consider connecting the Paginated Report directly to BigQuery via an ODBC connection or using a shared dataset designed specifically for reporting, where parameters are embedded directly in SQL or M queries. Alternatively, if you must keep the Semantic Model, you can create measures or calculated tables that include the date filter logic inside the model itself, or use Aggregations to minimize data volume. The goal is to ensure that filtering happens at the source level (BigQuery) rather than in Power BI’s intermediate layers.
Hi Poojara_D12 ,
thanks for your answer.
I don't need to keep the semantic model - I created it solely for this report. (Under the assumption that the filters would be pushed down to BigQuery).
I can't use an ODBC connection.
I could create a shared dataset specifically for this report - but when I get this right there is currently no way to query the Big Query directly with sql (including filtering data to the amount i only need for the report) .
Hi m_andel
Since your only goal in creating the Semantic Model was to connect Paginated Reports to BigQuery — and ODBC isn’t an option, the challenge is that Power BI currently doesn’t support direct SQL-style parameterized queries against BigQuery from within Paginated Reports. When you use a Semantic Model in DirectQuery mode, Power BI’s internal DAX translation layer limits how effectively filters are pushed down, which is why you see all 13M rows being retrieved before filtering. The most practical alternative is to connect the Paginated Report directly to BigQuery using the built-in BigQuery connector (available under the “Google BigQuery” data source in Report Builder) rather than going through a Semantic Model. This lets you write native SQL queries with report parameters directly embedded in the query (for example, filtering by date before execution), ensuring that only the necessary data is returned. If that connector isn’t available in your environment, you could also build a Power BI dataflow or Fabric data pipeline to pre-filter and materialize a smaller table or view in BigQuery, then point your Paginated Report to that optimized dataset. The key is to move filtering and shaping logic as close to the BigQuery source as possible to minimize data transfer and improve performance.
Hi Poojara_D12 ,
I finally found out how to Query Google BigQuery:
I created a datasource with all the columns from my BigQuery View, but when I try to use this datasource in a DataSet I can not enter a query. And I cannot modify this datasource after i saved it. (That's not very comfortable.)
What am I missing here?
Hi m_andel,
You are not missing anything, that is the current design. Power Query handles all query logic internally so the sql text box in the dataset properties is disabled.
So to customize your query or add parameters, edit it directly inside the Power Query editor using Value.NativeQuery.Inside the M code use a parameterized native query like this:
let
Source = Value.NativeQuery(
BigQuery.Database(null),
"SELECT * FROM ENTER_NAME WHERE Date >= @StartDate AND Date <= @EndDate",
[StartDate = Parameters!StartDate.Value, EndDate = Parameters!EndDate.Value]
)
in
Source
Save and close the power query the dataset will now return filtered data directly from BigQuery.
Thanks and regards,
Anjan Kumar Chippa
