Power BI Dynamic Subscription: Send Scheduled Report Filtered for Everyone’s Data Automatically

If you ever want to have a general report for sales for all countries, and then you want every morning the report to be sent (as PDF or PowerPoint alongside the link to the report) to representatives of each country, the report with the data filtered for their country only, then Dynamic Subscription in Power BI is the feature you need to use. Previously, this was called a Data-Driven Subscription in SSRS (SQL Server Reporting Services). In this article and video, I’ll explain how to use this feature using an example and what you need to know about it.

Video

What is Dynamic Subscription?

Dynamic Subscription in Power BI is one of those features that the name isn’t as expressive as other things. Here is what the Dynamic subscription is for; Imagine you have built a report that covers the data of multiple departments, branches, cities, countries, people, or anything that can be categorized. And then you want this report to be scheduled to be sent to representatives of those departments, branches, countries, etc. Well, so far, this is possible with a normal subscription in the Power BI report. we had that feature for many years already in the service. But the point is that what if the report export you want to send to those representatives isn’t going to be the general view, but it is a tailored filtered view for their department, branch, or country. so that the sales rep of Canada receives a PDF export of the Power BI report filtered for Canada only, and the sales rep of Australia gets her PDF export only for Australia, and so on. They will receive a filtered export based on their department or the category they are in, this is called Dynamic Subscription in Power BI.

Back in the days of SSRS (SQL Server Reporting Services), this feature was called Data-Driven Subscription. Still, I don’t think that name was fully self-explanatory either.

Dynamic Subscription in Action

Using this feature, you don’t need to generate multiple copies of the same report but with different filters every morning and send it to the sales reps manually. Dynamic Subscription will automatically do it for you.

Now that you know what the Dynamic Subscription does, let’s have a look at how you can use it.

Sample report

I have created a sample report using the AdventureWorksDW Excel data source, my report includes data from two tables below; DimSalesTerritory and FactResellerSales

The table that includes categories (which I want to filter using the Dynamic Subscription) is DimSalesTerritory and the category I want to use is the Sales TerritoryCountry;

I used the data in the two tables to generate a simple report that shows Sales Amount (from FactInternetSales) by Country and also by Region.

The goal here is to generate exports of this report for sales reps of each country on a scheduled basis using the Dynamic Subscription.

One thing to note is that nothing special is needed when you build your report to get it eligible for the Dynamic Subscription. You don’t need to create filters based on the Country field, you don’t need to create parameters or anything. Just build your report as you do always.

Set up Dynamic Subscription

Dynamic Subscription has to be set up in the Power BI service, you can’t set it up in the Desktop. Once you open your report in the service, choose the Subscribe to report option.

Choose the Dynamic per recipient option. The Standard option is for scheduling a report subscription without a per-recipient filter.

Recipient Data Table

The most important step in setting up the Dynamic Subscription is to have a data table that includes the recipient’s data. This table must include two fields at least; the email address in which they will receive the subscribed report, and the field that will filter the Power BI report using it (in our example, that field for filtering should be SalesTerritoryCountry).

The recipient data table has to be loaded into a Power BI semantic model (which is one of the limitations of this feature that I don’t like). That semantic model can be the same model as your current Power BI file, or it can be different.

I have created a recipient data table like the one below and called it the Mapping Table.

The table above lives inside the same Power BI semantic model that I use for the report. however, don’t need to filter any of the tables, it can be separate for itself. Remember that this table can be even in another semantic model too.

The Country field of this table is going to filter the SalesTerritoryCountry, and the rep email address will be used for the subscription emails.

You can choose the data table when you start the dynamic subscription and choose Dynamic per recipient.

Then you can choose the mapping table in the step of Select and filter data.

Setting up the Email details is simple. The main thing to set is the Recipients to be changed from Enter manually to Get from data and then choose Mapping table.Rep email address (which are the table name and the column names in the mapping table for the email address).

You can use the same approach for the email subject too. But that is if you want a different subject for each recipient. If that is the case, I suggest creating a calculated column in that table in Power BI that generates a nice email subject. For this example, I just use the Country.

You can also choose the type of export. The attachment file can also come from the mapping table if you need per recipient custom attachments.

The Map your data step is another important step here. This is the place where you choose which field from the recipient’s data table is going to filter which field in the Power BI report. In our example, the Country column from the Mapping table filters the SalesTerritoryCountry from the DimSalesTerritory table.

And then the last step is to set the schedule. You can run this as frequently as once a day.

It can be also configured in a way that it automatically runs after the semantic model refreshes (but not more than once a day).

That’s it. The Dynamic Subscription is now set for our sample report.

Testing the result

Based on my configuration, each recipient will receive their copy as a PDF export of the report at 4:15 pm every day. The time might not be exact, but it will be close to it. Here is the email received by the recipient of USA.

And the report is filtered for the United States as you can see;

That’s it. Here is the Dynamic Subscription working just fine. but there is one more thing to note.

Giving access to the report

Although it might seem this way, the Power BI report hasn’t been shared with those users yet. Power BI service needs a way to give access to the users. Setting up the recipient data table in the Dynamic Subscription set up unfortunately won’t add them automatically to the report access permission. Especially because there are many ways that you can share the report with the end users. You might have created an App and shared the content using an audience in the app. Whatever method you prefer to use for sharing, make sure that those recipients of the Dynamic Subscription are given access there, because otherwise if they want to open the link to the report, they will get a message saying that they don’t have access to it.

Licensing: Premium is needed

This feature at the moment is only reserved for Premium and Fabric capacities. You cannot have this with Power BI Pro only. To learn more about licensing in Power BI, read my article here.

Some limitations, and the Things I’d prefer to see

The Dynamic Subscription is a very new feature in Power BI, it is still at the preview stage at the time of writing this article, which means we can expect enhancements happening on it before it becomes generally available. Here are some of the limitations at the moment;

• Recipients cannot be more than 50.
• Receiving the subscription won’t mean having access to the report, you have to set that up separately (One of the things I really like to be automatic)
• The recipient’s data table has to be loaded into a Power BI semantic model first. However, I like it because it centralizes the fact that no data is used unless it is in a semantic model. but having the flexibility of just loading an SQL Server table or an Excel file there would be nice to have.
• I like to see expressions used in the Dynamic Subscription setup. For example when you generate the email subject or email message itself. If there was the possibility of using expressions combined with the fields in the recipient’s data table, then I could easily build a customized email subject and message. At the moment, you can only build that inside the semantic model that would be only for email subjects. I like to see more enhancements here for sure.

Dynamic Subscription is different from Row-Level Security.

If you had that in the back of your mind, here I answer it then. Dynamic Row-Level Security and Dynamic Subscriptions are two different things. They are not the same. Dynamic Row-Level Security is when you want each user to see their own data and not be able to see other users’ data, it is not filtering, it is the security of the data. Dynamic Subscription on the other hand has nothing to do with security, each user (assuming to have access to the report) can see other user’s data when they open the report. Dynamic Subscription is for sending snapshots of the report to the users with their filtered view of the report.

To learn more about Dynamic Row-Level security, read my articles here.

Dynamic Subscription can be done for Paginated Reports, too.

Last but not least; Dynamic Subscription is for both types of reports in Power BI; The normal Power BI reports (the sample you saw in this article), and Power BI Paginated Reports. The process is pretty much the same for the Paginated Reports with the difference that to create the mapping for the Dynamic Subscription, you will need to create and use Parameters.

Summary

In Summary, Dynamic Subscription allows you to create filtered exported snapshots of the Power BI report and send it to different recipients automatically. Although we had that feature for many years in the old SSRS, it took some time to get it in the Power BI report. The feature is simple to use, but since it is at the preview stage yet, there is room for enhancements to be done. I suggest you give it a try and let me know what you think of it, what are the scenarios you would use this feature for?

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.