Power BI Paginated Report with BigQuery DirectQuery – Filters not pushed down

[m_andel]

Hi everyone,
I’m currently facing the following scenario:

My data resides in BigQuery, with potentially quite a large volume (up to ~13M rows). To simplify external queries, I’ve created a view in BigQuery.

This data needs to be presented in a Power BI Paginated Report. As the connection between BigQuery and the Power BI Service, I’m using a Semantic Model with DirectQuery, hosted in the Power BI Service.

The Paginated Report uses parameters to restrict the 13M rows, with a date column being the most effective filter.

My assumption was that running a DAX query against the Semantic Model in the Paginated Report would push the parameterized query down to BigQuery. However, when checking the BigQuery logs, I noticed that the query is executed without the restrictive WHERE clause. It seems the Semantic Model queries BigQuery first and only applies the DAX filters afterwards.

Is there an alternative, more performant approach for this scenario?

Best regards

[Poojara_D12]

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.

[m_andel]

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) . 

[Poojara_D12]

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.

[m_andel]

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? 

[v-achippa]

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

[m_andel]

Hi, @v-achippa 

I can now load data. My last issue is the connection to powerbi. Is it possible to use a google service account with a key file? I have found no option to configure the connection this way. 

[v-achippa]

Hi @m_andel,

Currently using a google service account key file is not supported in Power Query based connections within Power BI Report Builder or the Power BI Service. Only OAuth authentication with a user account is supported, so please use OAuth sign-in for now.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @m_andel,

As we haven’t heard back from you we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[m_andel]

Hi, @v-achippa ,

unfortunately there seems to be no solution to realize paginated reports the way we need to. Our next step would be to create such reports in Power BI Desktop. 

As I understand there it is possible to query the BigQuery and use a service account for authentication. 

And I suppose it is possible to design reports that feel just like paginated reports?

Kind regards,

Martin

[v-achippa]

Hi @m_andel,

Yes power bi desktop can connect to BigQuery using a service account, so that part will work.

However, power bi desktop is not a full replacement for Paginated Reports. You can create reports that look similar but desktop is designed for interactive visuals and does not support the same pixel perfect pagination, repeating groups and print ready layout capabilities that Paginated Reports provides.

Thanks and regards,

Anjan Kumar Chippa

[m_andel]

Hi, @v-achippa ,

thanks for the info. I will see how this fits for our purposes, in fact our reports are not meant for being printed out. Maybe i will miss the repeating groups. 

Do you know, if authentication on BigQuery with service accounts in the Power BI report builder will be possible in the near future?

[v-achippa]

Hi @m_andel,

Currently microsoft has not made any announcements about adding service-account authentication for BigQuery connections in Power BI Report Builder or Paginated Reports. The current Power Query connector only supports OAuth, and that’s the only officially documented method.

If this is important for your scenario, I recommend submitting your detailed feedback and ideas through Microsoft's official feedback channels. Feedback submitted through these channels is frequently reviewed by the product teams and can contribute to meaningful improvements.

Fabric Ideas - Microsoft Fabric Community

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @m_andel,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @BeaBF for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[BeaBF]

@m_andel Hi!

• Paginated Reports query the semantic model, not the data source directly.

• Your DAX query + parameters in the Paginated Report first resolve against the semantic model.

• The BigQuery connector in Power BI has limited predicate pushdown capabilities, especially when queries originate as DAX. That means you often see “SELECT *”–style queries from the source, followed by filtering happening afterwards in the Power BI engine.

• This is why your BigQuery logs show queries without the expected WHERE clause.

WORKAROUNDS:

1. Parameterize the data source query, not just the report

Instead of relying on DAX to push filters down, you can:

• Create a BigQuery parameterized view or stored procedure that already expects the date filter.

• Point the Paginated Report directly at that query (using a shared dataset or dataset parameters), so the filter gets applied at the SQL layer.

This avoids the semantic model doing the heavy lifting.

2. Use Paginated Report direct BigQuery connection

Paginated Reports don’t have to go through a semantic model. You can connect them directly to BigQuery (via ODBC/JDBC or the Power BI connector).

• Advantage: filters in the report parameters will get translated into the SQL query and pushed down.

• Downside: you lose reuse of the existing semantic model.

If performance is critical, this is usually the best option.

3. Pre-aggregate or partition data in BigQuery

• If you must stay with the semantic model, restructure your BigQuery layer.

• Partition the view on the date column and only scan the necessary partitions.

• Materialize a smaller table or partitioned dataset that’s directly filterable.

That way, even if Power BI pulls “too much” data, BigQuery itself is handling less.

4. Consider Import mode (if data volumes allow)

• If the data reduces significantly after filtering (e.g., 13M rows daily but only ~50k are relevant at report time), Import mode with scheduled refreshes might outperform DirectQuery for Paginated Reports.

• You can then apply filters in-memory efficiently.

• But this depends on whether freshness requirements allow a refresh delay.

5. Use Composite Models / Aggregations

• Define an aggregation table (pre-filtered or aggregated by date) in BigQuery.

• Let the semantic model map user queries to the aggregation where possible.

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[m_andel]

Hi @BeaBF, 
Thanks a lot for your response! I’ve got a few follow-up questions and comments regarding your points:

1. That would require an ODBC connection, right? Unfortunately, I can’t use ODBC. The whole reason I’m connecting my Power BI reports directly to BigQuery is to avoid the need for a gateway server.

2. I only created the semantic model for these reports.
   This solution (using ODBC) would basically be the same as option 1, correct? In Power BI Report Builder, I can’t find a “Power BI connector” data source option. If there is a way to connect directly to BigQuery from there, could you point me to where/how to set that up?

3./5. The data is already partitioned by day. But without the right SQL query being pushed down, that doesn’t really help me. Further pre-aggregations aren’t possible at the moment (or at least I’d need to discuss that with the project team first).

1. In this report, users can select larger time ranges, so I can’t really predict how many rows will be needed. Is there any way for a Dataflow to fetch missing data from the source dynamically, as needed? (As far as I know, that’s not possible.)

2. Pre-aggregation might be an option if we revise the report’s purpose — that’s something I’ll need to clarify internally.

To sum it up:

I need to run a parameterized query from a Paginated SSRS Report against BigQuery data, without using an ODBC connection and without copying the data into a Power BI Dataflow first.
From what I understand so far, it seems that there’s currently no way to achieve this.

I appreciate any confirmation or workaround ideas from the community!

[v-achippa]

Hi @m_andel,

The behaviour here is expected because the paginated reports that connect to a power bi semantic model execute DAX queries against that model and the BigQuery connector currently has limited predicate pushdown support for DAX. That is why your BigQuery logs show full table scans without the WHERE clause.

To make the filters execute in BigQuery without ODBC or dataflows, connect the Paginated Report directly to BigQuery using Power Query:

• In Report Builder, create a new dataset using Power Query.
• Use a parameterized native query via Value.NativeQuery and pass your date parameters so that BigQuery runs the filtered SQL directly.
• This runs fully cloud to cloud and no gateway or ODBC needed and the filter is pushed down to BigQuery.

This is currently the only supported approach to make sure parameter filters are executed in BigQuery when using Paginated Reports.

Thanks and regards,

Anjan Kumar Chippa

[m_andel]

I can't find Power Query as a ConnectionType

[v-achippa]

Hi @m_andel,

The Power Query connector option is only available in the new Power BI Report Builder, older or standalone report builder versions do not show Power Query as a data source type.

Please download and install the latest Power BI Report Builder, after installing you will be able to see the Power Query option

Thanks and regards,

Anjan Kumar Chippa

[m_andel]

You are talking about this version, right?

Download Microsoft® Power BI Report Builder from Official Microsoft Download Center

(15.7.1815.334)

I have already installed that version.

If I am not using the correct app  - you have written "standalone report builder versions" - I am not aware that there is another one- please provide me a link.

[v-achippa]

Hi @m_andel,

We wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa