DirectQuery - data missing on app.powerbi.com

[tez]

I created a DirectQuery dashboard for timed data (helpdesk ticket number per hour, bar chart). Data is displayed correctly at Power BI Desktop. When I publish it to app.powerbi.com and connect it to my dataset, it´s still all ok.

But after a few hours update seems to go wrong: New data is about 50% too low in app.powerbi.com, while it is correct displaying at Power BI Desktop. Even if I do a manual refresh, it doesn´t get better.

Do you have an advice, how I can get a clue what goes wrong? Does anyone have an idea of what might be the cause?

Thanks,
Thomas.

[v-lid-msft]

Hi @tez ,

Do you mean view report or dashboard in Power BI Service, or use embedded / publish to web? Have you tried to refresh the visual of report as following?

If you mean tile pinned in dashboard, have you tried to configure "scheduled cache refresh" to "every 15 minutes" in dataset setting?

Best regards,

[tez]

Hi,

thank you for your reply. It is a report in Power BI Service. And yes, I tried to refresh. The dataset cache refresh is set to "every 15 minutes" from the beginning.

Is there a possibility to get insights about the refresh mechanisms of this specific report in Power BI Service?

Regards,

Thomas.

[v-lid-msft]

Hi @tez ,

Please refer to this document about  the scheduled cache refresh and Direct Query, are you using on-premises data gateway or connecting from power bi service directly? Does this report have a heavy load or the data source is busy? Could you estimate approximately How much time the report is behind of the real data?

Best regards,

[tez]

Hi @v-lid-msft,

thank you for the links, I was aware of them.

I´m using an enterprise on premise gateway (not personal); there is NO premium, only an Enterprise Office 365 license and my Power BI Pro license. The report has a filtered query for the last days, so it has around 2000 lines, a small dataset.

Here are visualizations; this is the correct visualization:

This is the visualization as shown in power bi service with missing data, the same time of refresh:

There is data missing at different timestamps.

But I found out something:

• My query has different tables, which were merged (NestedJoin and ExpandTableColumn). A master table (about 2000 entries, 5 small tables to join (one about 2000 entries, the others about 300 entries)
• It has storage mode "direct query" (shown at the right lower corner in Power BI Desktop)
• If I remove the tables to join and use only the master table (without joins), the refresh works as expected and shows the correct data.
• The original pbix with the joins shows the wrong data (as shown above), even if it confirms DirectQuery mode; even a manual refresh does not show the correct data in Power BI Service; but within Power BI Desktop everything is shown correctly after a manual refresh.
• I do not see any error messages (mail address is configured to be informed within cache refresh).
• If I change the pbix (with the joins) from DirectQuery to Import mode and schedule refreshs, everything works fine.

From my point of view it seems that there is some problem with joins and DirectQuery. Do you have any idea?

Note: I will not be able to reply until April 15, or i do reply very rarely; after that I will get back in touch.

[tez · Accepted solution]

I think I found the problem. I had a filter in the query:

= Table.SelectRows(dbo_CAL_IncAndSr, each [Field0] = 0 or Date.IsInCurrentDay([Field1]) or Date.IsInPreviousNDays([Field1], 21) or Date.IsInCurrentDay([Field2]) or Date.IsInPreviousNDays([Field2], 21))

As soon as I removed the filter, the update - until now - worked without any problems. Maybe this is also interesting for other users.