Direct query instead of mixed mode - refresh question

[maya13]

Hi All,

I would like my report to refresh automatically when published into Power BI workspace. As I understand it correctly only direct queries datasets can be refreshed automatically (assuming all the data is in the cloud).

I have built a report based on data from our NAV server (get data from server). The only table I added not from the server was a date table. In my desktop Power BI  in the bottom right corner I am getting a message that storage mode is mixed. Will that be a problem to get the report refreshed automaticaly when published (working live)?

When I go to settings of that dataset it says all the dataset is in the could and I do not need a gateway but still the report does not seem to refresh every half an hour or so.

Thank you.

Maya

[v-yuezhe-msft]

@maya13,
 
What  are the data sources in your dataset? Please go to File->Options and Settings-> Data source Settings in Power BI Desktop to check all data sources and post a screenshot about them.

In Power BI Desktop, it doesn't support DirectQuery mode for NAV data source,please check all the data sources supported by DirectQuery in this article.

In addition, when you have mix storage mode in the PBIX file, you are limited to refresh 8 times a day in Power BI Service unless you have Power BI premium license. Only when you have direct query /connect live mode in the PBIX file, you can refresh the dataset every half an hour.

Regards,
Lydia

[maya13]

Hi Lydia,

The data source is our sever that is in Azure, sorry I was not clear enough before. So basically all data is in Azure except for if the date table. Is there any date table/calendar (built into power BI) that I can use to keep the direct query format of my report and to be able to refresh live. How do you overcome the issue of the calendar table?

[v-yuezhe-msft · Accepted solution]

@maya13,

You would need to create the calendar table in Azure SQL database which you use DirectQuery mode. This way, import the calendar table in Power BI Desktop, you can keep live mode as you describe.

Regards,
Lydia