Writing calculations on a real-time streaming Power BI dataset using DAX measures

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
writing DAX measures on a real-time streaming dataset

In the previous article and video, I explained a method that you can use to have real-time calculations in a Power BI streaming dataset. The calculations done that way are limited though because it is using the Q&A feature of Power BI. If you are after a complex calculation then DAX can definitely help. In this article and video, I’ll explain a method that you can use to write DAX measures on a Power BI streaming dataset.

What is a streaming dataset?

In Power BI, you can have a dataset with Imported dataDirectQueryLive Connection, or Composite mode. You can build all of those types of Power BI datasets in the Power BI Desktop. However, there is a single type of dataset, which you can only build through the service, called the Streaming dataset.

A streaming dataset is for building reports with real-time response time. For example, if you want to build a Power BI dashboard that shows the room temperature as soon as captured by a temperature sensor. For this type of dataset, you send the data rows using Power BI REST API, which can be called using a custom C# application, or PowerShell scripts, or even from a Power Automate flow process.

API information for a streaming Power BI dataset

Streaming dataset can be only created using Power BI service.

Creating Power BI streaming dataset

The fields in an streaming dataset will be defined in the service too. And all of the fields will be under one table.

Defining fields for the streaming dataset

This is not a post about how to create a streaming dataset, You can read more about it here.

No Power BI Desktop

The big development problem with the streaming dataset is that you don’t have a PBIX file. You cannot open this solution in Power BI Desktop. And as a result, you will loose the ability to bring other datasets, modify and edit the data using Power Query, and write calculations using DAX.

You cannot download a PBIX file of a streaming Power BI dataset

The download option is not even available from the dataset.

you cannot download a Power BI streaming dataset or report

Live Connection to the streaming dataset

If the calculation you are after is a complex dynamic calculation, then you can write DAX expressions for it. This can be done using a live connection to the streaming dataset using Power BI Desktop:

Get data from Power BI dataset

The streaming dataset can be used as the source of live connection.

choosing streaming dataset as the source of live connection

This would create a live connection to the streaming dataset.

Live connection created to the streaming dataset

When the live connection is created, you can write DAX measures either using normal measures or quick measures.

writing DAX measures on a live connection to Power BI streaming dataset

Would it be real-time?

One important consideration here is that the report generated this way, wouldn’t be a real-time report or dashboard. This would be a live connection to the real-time dataset. Which means any dashboard created on top of it would be refreshed with the frequency that can be set at the dashboard level. And every time you browse the report, you will have the report from the data at that moment.

DirectQuery to the streaming dataset: Even more flexibility

The ability to write measures is great. However, sometimes, you want to create columns too, and maybe bring another data table to combine with this data. With the new feature released named as DirectQuery to Power BI dataset, you can do this.

Changes in the Model using DirectQuery to Power BI dataset

You can have a table as the DirectQuery connection to the streaming Power BI dataset, this will give you the ability to bring other data tables as imported and have a more complete solution.

DirectQuery to Power BI streaming dataset

To learn more about how the DirectQuery to Power BI dataset works, read my article here.

Q&A for simple real-time calculations

A reminder of my previous article here that if you are looking for a calculation for your real-time dashboard and the calculation is simple, you can use Q&A feature in the report. Read more about it here;

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply