Power BI is an extremely user friendly and flexible analytics, reporting, and dashboarding tool. In the past two years, I’ve witnessed the massive adoption of Power BI by my clients and in the marketplace, often replacing more expensive tools due to its ease of use and low barrier to entry.
As with any software, there are certain drawbacks that may limit the use or adoption of Power BI in specific situations. On a recent project, my team was asked to develop a real-time report using a Power BI DirectQuery connection. This report was to be filtered with a slicer for a particular work-center, displayed on a user’s monitor on a manufacturing shop floor, and wasn’t going to be interacted with by that user. Lastly, the data was expected to be refreshed at least once per minute as the manufacturing shop floor will be interacting with the data. Power BI’s data refresh documentation mentions:
“When you interact with a visualization, queries are sent from Power BI directly to the database. Updated data is then returned and visualizations are updated. Because there is a direct connection between Power BI and the database, there is no need to schedule a refresh.”
But what if my user does not interact with the data? Again, there is an answer to that:
“For the DirectQuery/Live connection, you can set the refresh frequency from 15 minutes to weekly using the associated drop-down menu, as shown in the following image.”
But our requirements specified that the report needed to be updated at least once per minute. There was nothing in the documentation about this. There are several posts on the Power BI community (like this one) that discuss Chrome extensions to auto-F5 to refresh the page. But in this case, the requirements call for the user to choose from a work-center on a slicer and leave the page alone. Refreshing the browser causes the user to lose their slicer selection. This left us having to create a different solution.
When inspecting the Power BI report page, the refresh button on the Power BI service could be clicked to refresh the data without impacting the selected slicers.
The code snippet above can be added as a bookmark with the desired interval (red number in milliseconds) to refresh at the user’s preference. Once the page is set with the right slicers, the user can click the bookmark and the page refreshes automatically at the desired interval. This code also clears out any other “autoRefreshInterval” that is running, so you don’t end up with 15 timers all counting down to refresh your page if the user accidentally clicks the bookmark multiple times.
To add a bookmark, see the instructions below:
|Press Ctrl (PC)/Command (MAC) + D on the browser and save a bookmark of this page|
|Click on the Favorites drop down menu at the top of the browser and select Organize Favorites||Select the Bookmarks drop-down menu, and click Show All Bookmarks||Click on the wrench or … icon in the upper right of the browser and navigate to Bookmarks>Bookmark Manager||Select the Bookmarks drop-down menu, and click Show All Bookmarks|
|Right-click the new bookmark and select Properties||Select your new bookmark||Click the … next to your bookmark||Right click your new bookmark|
|Copy the code above and paste it in the URL box for your bookmark|
Power BI Out-of-the-Box Solutions
Power BI is an extremely powerful analytics and visualization tool; however, there may be some things you feel are drawbacks that may need a solution that isn’t out of the box functionality. Hopefully, this tip will help you achieve your needs for refreshing a Power BI live report through DirectQuery mode.
Have any other questions about Power BI or other analytics and business intelligence tools? Please contact us at firstname.lastname@example.org. We would love to discuss potential solutions with you and hear about your experiences.