Addressing Query Date Range Sync Challenges in Power BI Connected to BigQuery

[skhatib]

Hello 

I'm new to Power BI and encountering an issue. I've connected a dataset via BigQuery (direct query) in Power BI Desktop. Since the dataset is large, I've included a WHERE clause in my query to limit the data I'm working with in the power bi desktop , thus reducing the impact on BigQuery.

Here's my query:

```sql
SELECT
DISTINCT DATE(insertdate) AS insertdate,
colum1,
colum2,
colum3,
Type
FROM
`tablename`
WHERE
colum1='AED'
AND DATE(insertdate) BETWEEN '2024-01-01' AND '2024-01-10'
```

Additionally, I have a date table with `calendarauto()` connected to this fact table. I've completed the visualizations, and after publishing to Power BI service, I only see data visible for the specified period. However, when I change the slicer date, the query sent to BigQuery still has the same fixed date range between '2024-01-01' and '2024-01-10'. The slicer date doesn't seem to sync with the request to the database.

date table and insertdate both have data type as date and also date table is marked as date

I dont know what is wrong with this , can someone help me 

[GilbertQ]

Hi @skhatib 

Yes you would if you wanted to bring in the data and let it run quite quickly.

[GilbertQ]

Hi @skhatib 

What you need to do is to configure your Power Bi desktop with incremental refresh. This will then allow you to load all the day to that you need into your power BI semantic model. Here is a link explaining what incremental refresh is Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn 

[skhatib]

Hi @GilbertQ , Thank you for getting back to me. To implement incremental refresh, I'll need to switch my model to import mode. However, due to the large volume of data, import mode isn't feasible. I'm currently utilizing direct query. Shouldn't this setup work fine with the date table when connected to the semantic model and selecting dates in the slicer?