Forum Discussion

m_andel's avatar
m_andel
Helper I
10 months ago

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

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 ...
Need Help
Tips and Tricks
v-achippa's avatar
v-achippa
Community Support
10 months ago

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

v-achippa's avatar
v-achippa
Community Support
10 months ago

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