Have you ever tried to send out personalized/ filtered Power BI reports to all Sales managers for all branches daily or weekly all at once? In the past, this would have been tricky to achieve, however, with the recently released feature called Dynamic Subscription in Power BI service, you can achieve this and even more seamlessly. In this article, we will explore the functionalities of this great feature.

What is Dynamic per Recipient Subscription?

Dynamic subscription is a feature in Power BI Service that allows you to generate and send customized copies of a report as a PDF file or a PPT file automatically. Every report is built specifically for the end user. To ensure the report displays only information relevant to the user’s needs, you must apply filters during the setup.

How it works!

With this feature, you would no longer need to create filtered copies of a report to be sent to stakeholders daily, weekly, or monthly. Dynamic per recipient subscription does that for you.

Prerequisites

Workspace and Licensing: The report has to be published in a workspace backed by a Premium capacity or a Microsoft Fabric capacity, the Microsoft Fabric trial version also works at the moment.

Permissions: You must be able to edit published reports (semantic model) i.e. You have to have either a member, contributor or admin role in the workspace.

Now let’s take a look at how this feature works.

Setup and Configuration.

Since the feature is a Power BI Service feature, most of the setup will be done in Power BI service. However, you may need to set up the mapping table on Power BI desktop.

What happens in Power BI desktop?

Let’s use this sample data model and report for illustration purposes.

In the schema above, we have a Region dimension table (Dim_Region) which is what we want our data (Dynamic Subscription) to be filtered by.

The data model was used to build the report below.

We have a chart that shows total revenue across regions, the goal is to share a filtered version of this report to all recipients on a scheduled basis using Dynamic per recipient subscription. To achieve this, we will need a special table that contains the recipient’s data.

Recipient Data Table

This table acts as a foundation for setting up the dynamic subscription. It can be imported from a spreadsheet or database or created directly in Power BI. It must contain two essential columns:

• Recipient email: Email address in which end-users will receive the filtered report and the;
• Filter Criteria: A field that will filter the Power BI report (in our example, that field for filtering should be region).

The recipient data table has to be loaded into a Power BI semantic model. This table can be included in the same model as your current Power BI file or in a separate semantic model. Note that the table can be a stand-alone and not related to any table in the model. Check out the model image above.

Below is a snapshot of the recipient data table I created, called “Mapping Table”. I also added a column for the mail subject.

Setting up the Dynamic per Recipient Report

In Power BI service, after publishing the report into a premium capacity workspace or a Fabric workspace, you need to open the report and navigate to the “subscribe to this report” option.

Next, you’ll have two options before you, standard and Dynamic per recipient subscription, in this case, we’ll choose the second option.

Next, you need to choose the semantic model which must contain the recipient’s data.

The next step is where it gets interesting, we select the table that contains the recipient’s data, in our case it’s the mapping table.

Next, we need to provide the email addresses (of the recipients) that the filtered Snapshot report and (a PDF) version will be sent. This can be done either manually or automatically obtained from the data model. You can use the same approach for the email subject too.

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 also very important. Here, we choose the field from the recipient’s data table that is going to filter what field in the Power BI report. In our example, the region column from the Mapping table filters the Region column from the Dim_Region table.

The final step is to set up the schedule. You can run the frequency to be daily, weekly monthly or even after the data (semantic model) refreshes but not more than once a day. In our case, we set the repeat time to weekly, every Monday and Wednesday by 8:30 am.

Once everything is set up, you can save and close.

Testing

In the current setup, recipients will automatically receive a PDF copy of the report around 8:30 am each day. The timing might vary slightly, but it should be close. Below is an example of what a recipient in the North East region receives. The email comes with a filtered snapshot as well as a filtered PDF copy of the report.

One final step – giving access to the report.

Although the above steps are sufficient to set up the Dynamic Subscription Report, it is worthy to note that this report has not been shared with end-users yet. Once the end-user gets the email and clicks to view the report, he will get a message requesting access like the image below.

The report builder on the other hand has to grant all users access to the report. You may receive an email like the one in the image below. Setting up the recipient data table in the Dynamic Subscription alone doesn’t automatically give user access, this is somewhat a limitation.

Some Limitations to be aware of

• Hiding a table is not advisable: If a table is hidden in Power BI desktop, it will not be available for you to create a dynamic subscription.
• Number of recipients: As of the time of writing this article, recipients cannot be more than 50.
• Semantic Model: The recipient’s data table has to be loaded first into the semantic model.
• Column and table names: If the names of columns or tables are changed in the semantic model while the subscription is processing, dynamic filters might not be applied properly.

How is this different from Row Level Security?

If you may be wondering how Dynamic Subscription differs from Row-level Security, the clear answer is that dynamic subscription and row-level security are entirely different concepts. In simple terms, row-level security provides a security layer on your report which means that it protects users from seeing data that does not concern them. Dynamic subscription on the other hand, just provides a filtered version of the report as a snapshot and or a PDF copy, if the user is given access to the report, he sees the unfiltered version i.e. the whole report.

If you prefer users to have restricted access to only their data within the report, you will need to apply Row-level security first before setting up the Dynamic Subscription.

Summary

To wrap up, Dynamic subscription is a relatively easy-to-use feature that allows users to create and send filtered snapshots of the Power BI report to different users automatically. This feature is also available for Paginated reports. In conclusion, this approach simplifies report delivery while guaranteeing relevant and actionable insights for a diverse audience. I suggest you try this feature out and let me know what your thoughts are, what are the scenarios you would use this feature for?

If you’ll prefer to see this feature in action, you can watch it here.