Forum Discussion

tez's avatar
tez
Resolver I
6 years ago
Solved

DirectQuery - data missing on app.powerbi.com

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 ...
Need Help
  • tez's avatar
    tez
    6 years ago

    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.

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

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's avatar
    tez
    Resolver I
    6 years ago

    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's avatar
      v-lid-msft
      Community Support
      6 years ago

      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's avatar
        tez
        Resolver I
        6 years ago

        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.