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.
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.
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.
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.
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.
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.
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.
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.
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;
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.
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.
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 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.
Hello, I can’t choose the “Dynamic per recipient” option when i create a new Subscription, this type “dynamic” not appears on my screen !! Why ??
