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

m_andel's avatar
m_andel
Helper I
9 months ago

I can't find Power Query as a ConnectionType

 

  • v-achippa's avatar
    v-achippa
    Community Support
    9 months ago

    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