Real Time reports in Power BI Service (Free & Pro)

Have you ever wished you could create a real time Power BI report? Me too! What if I told you that this is possible, and has been for years? Check out this demo where I am creating new orders in my database table while Power BI immediately shows the changes! (sorry for the potato-quality gifs)

Real time connectivity has been a major ask from Power BI consumers for some time. There are currently other BI tools on the market that allow for live connections to various data sources, but I can confidently say that Power BI is the best overall BI tool on the market despite its drawbacks. The October 2019 update brought a new feature titled “Automatic Page Refresh” with the purpose of refreshing your report pages when connected to data sources through DirectQuery. This effectively creates a “real time” connection if you set up the automatic page refreshes to an interval of say one second. In this example, the Power BI report would refresh your page every second, sending queries to your data source every second, and redisplaying the data every second.

I was super excited when I read about the new functionality. But as with some new features, there is a major drawback. For Power BI Pro, you can only set the page refresh interval to a minimum of 30 MINUTES in Power BI Service. This isn’t anywhere close to real time. Quick side note: 1 second page refresh intervals are only available for Power BI Desktop and Power BI Premium at the moment.

So, I was a bit disappointed after learning about the limitation on Power BI Service. But this new release did kickstart some ideas on how we could achieve true real time connections. After a bit of testing, I was able to make a real time Power BI report, and now I would like to share this trick with you!

The whole setup is very quick. You simply need a connection to a data source via the DirectQuery method and the Play Axis Slicer custom visual. In this demo report, I will quickly connect to my Orders table in an Azure SQL database via DirectQuery. Remember that only certain data sources support DirectQuery, list here: https://docs.microsoft.com/en-us/power-bi/desktop-directquery-data-sources

Now that we have a DirectQuery connection to a table, I will go ahead and add a few visuals to display the data that is currently available in the table. This isn’t all that necessary, but it will better prove our live connection later.

Next, we employ the help of the Play Axis custom visual from the custom visual marketplace. The Play Axis slicer let’s us step through rows in a table. Lucky for us, each time it moves to another row, it is going to kick off a refresh of the DirectQuery data source.

Let’s set up a random, disconnected table that our Play Axis will step through. Click on Modelling -> New Table and enter in the following code:

Play Axis = GENERATESERIES(1,100000)

This code creates a table with numbers from 1 to 100,000. You can make the range much smaller if desired, but I like a large range so that our Play Axis never has to repeat numbers. Next, add the Play Axis slicer to the report canvas and add the new “Value” column from our Play Axis table. Click the play button and the Play Axis slicer will step through the records, starting with 1. At this point, our live connection is already working but it is a bit hard to tell. To make things more obvious, I am going to add a PowerApp visual into the report that allows me to create new records in my Orders table.

Watch closely as I insert a couple new orders into the table for two new countries, India and Russia. Right after I click the “Submit” button, the DirectQuery data source will show our new rows!

You can clearly see how the Power BI report is able to immediately pick up on the new records that I entered into the database! That’s pretty much the entire trick! I hope you enjoy using this method to create live connections within your Power BI reports, on both Power BI Desktop and in Power BI Service for Free and Pro accounts!

A bit more info:

I want to take just a bit of time to outline a few points that might be frequently asked about in the future:

• You can change the speed at which the Play Axis Slicer iterates through the data in the formatting settings.
• I also turned on the “Auto Start” and “Loop” settings on the Play Axis slicer so that users will always have the live connection functionality turned on from the moment they update the report.
• I used such a large numeric range in the Play Axis table because the Play Axis slicer caches the data for the first run through each number. For example, if the Play Axis is on number 1 when I have 8 records in my table and then I add 3 records. I now have 11 records, but if the Play Axis slicer restarts and goes to number 1 again, it will only show 8 records. Therefore, I don’t want the Play Axis slicer to ever loop. I achieve this by making the numeric range so high.
• You can simply hide the Play Axis slicer behind some visuals so that it doesn’t have to show. If you hide it via the selection pane, it will no longer step through the data.