Daily dataset refresh not working after sundays

[ShaWo]

We have a report that is in mixed mode having couple tables in dual mode, couple in directquery and several others in import.

1. I create couple aggregate tables for different granuality levels to speed up the report from the directquery table

2. I use the original tables column in a matrix as a value and three different levels of datetime values as categories.

3. Upload the report to Power BI service and make a daily scheduled refresh plan for 8 times a day.

4. Report is working great for couple days.

Error: Following sunday/monday night at midnight the report refresh seems to break somehow because the following days aren't refreshing anymore. When I go to the dataset, it does say that the refreshes have gone through without any problems but the actual report isn't refreshing.

-> I try to make new report in the service from the said dataset but that too doesn't have new data in it, even though it should go through the aggregate tables to SQL server.

It doesn't seem to matter if i update the report by publishing it to the service on top of the old one on monday or friday, every week the scheduled refresh seems to break on sunday.

Right now, I'm updating the tables manually by refreshing the report in Power BI desktop on monday and uploading it on top of the old report in the workspace which also updates the dataset. After this the dataset refreshes until the next sunday.

All the tables come from Azure SQL server and don't need a gateway. One table is an calculated table which source is from an import table

I've made couple new models to test out the problem and I can't get rid of it. I've tried making calculated tables with dax instead of aggregate tables through manage aggregates. I've also tied simplyfying the model by removing dax-calendar tables. Report is refreshing when the report is in full directquery mode and also when it is in full import mode.
I'm baffled by this problem, because the refreshes are working 8 times per day until sunday midnight. I've also checked with the SQL server administrator that there aren't anything special going on on sundays compared to any other day of the week.

Has anybody else experienced this?

[v-cherch-msft]

Hi @ShaWo 

I cannot reproduce the same issue.I would suggest you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".

Regards,

[ShaWo]

Thanks, I've done this and have been in talks with the support for couple weeks now. Just wondering if anybody else have seen this kind of behaviour in their models.

[dkangel21]

Any solution to this?  I found out today we are having the same issue.  In service (not desktop) the dual mode doesn't always refresh properly.

[ShaWo]

Unfortunately we didn't really resolve this issue by fixing the afformentioned reports. We managed to get around the issua by going to full import mode and creating views for the data to be imported. The model still has calendars that are calculated on the fly and everything is working just fine.

We tried resolving the issue with microsoft support, but that wasn't really helpfull. Their suggestion was to create the views for the dataset, but if the model was on mixed mode the model just wasn't refreshing after sunday even with views. They dug up some statistics that some of the queries might be heavy so that it might be that reason. For me that sounds as a weird reason as that would mean the model wouldn't update any other day too.

Hopefully they find a proper reason for this as the mixed mode models are really useful. In our case the model reads about 5 million rows for two tables 8 times a day when it actually wouldn't need to download so many rows for the data to be up to date.

@dkangel21 If you get microsoft support to help you or find a solution, please update here. I'm very keen on learning what is happening with the mixed mode models even though there isn't any issues currently.