How to write simple calculations in a Power BI streaming dataset?

how to write calculations in Power BI streaming dataset

If you use a streaming dataset in Power BI, you cannot download the Power BI file, and you cannot open it using Power BI Desktop. This means that you are limited not to use calculations in a streaming dataset. However, there is a small trick which you can use and can be helpful. I will show you that in this article and video.

What is a streaming dataset?

In Power BI, you can have a dataset with Imported data, DirectQuery, Live 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

Using Q&A

Although, you cannot write calculations. You can use Q&A. Q&A in Power BI is a very useful way of analyzing the data and even building some visualizations by just asking questions. The Q&A is not only giving you the existing field’s list but also understands some calculations. So you can actually use it to create calculations in a streaming dataset.

using Q&A in Power BI service

As you can see in below, I can subtract the two fields “Salary” and “Salary pre-COVID-19” and add some more criteria in my visual. This would create the calculation and use it in that visual.

calculations in Q&A

When I convert that visual to a normal visual using the button below;

Turn the Q&A result into a standard Power BI visual

The standard visual will have the calculation in it;

calculation in a standard Power BI visual

This, will not of course support all types of calculation. I believe many complex scenarios are not supported. However, still, the ability to do a simple calculation in a streaming dataset (that otherwise there was no other way to do it) is great.

If the calculation that you are after is too complex for the Q&A, then your other method is to do the calculation before sending the data row to the streaming dataset.

I hope this quick tip helps you to develop better real-time streaming solutions using Power BI. If you have any questions, please don’t hesitate to ask in the comments below.

Video

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply