This article is a continuation of Sharing Power BI reports with authenticated users through our web application. We’ll be using the same sample we created there, which is available on this repository.

‍

One of the greatest features Power BI provides is drill through. That is, sending the viewer from one page of a report to another one based on related data both pages contain. This is useful in cases where the viewer wants to see detailed information for a particular entity listed on the source page; these details can be provided on a separate more focused page.

What’s even better is that you can not only do this on pages from the same report, but also on different reports. This can only be achieved on reports hosted on the Power BI service, so it doesn’t apply to Power BI Desktop.

In the following example we can see how this works; we have a source report listing the US states and then a target report listing its cities. If we right-click on any state, a Drill through menu appears allowing us to jump to the Cities report filtered by the selected state (in this case Alabama):

‍

‍

This is a nice feature available on the Power BI service but it has a major downside: It doesn’t work on Power BI Embedded, the target report won’t be loaded because it needs to be embedded.

There are also other disadvantages to consider when choosing this approach:

• Filtering occurs only if the source and target report datasets have the same table and column name for the filter being applied. In the example above, both States and Cities reports should have State[state_name] column displayed on the report, so the drill-through can work.
• If we have slicers on the target report, these won’t be updated with the filter we are passing on the drill through, this means that the implementation should avoid using slicers and employ another kind of visuals instead, like cards, for instance. But this really limits the potential of our report, doesn’t it?
• Right-clicking and going to the Drill through is not very intuitive for the user. Wouldn’t be better to have links for it?

In our sample, we’ll be tackling all these inconveniences.

‍

Hyperlinks instead of Drill Through

In order to avoid using native Power BI drill-throughs we need to come up with a much more intuitive alternative for the user that is navigating our web application, which is using hyperlinks.

Query strings parameters will help us achieve this feature we are looking for. These parameters get passed along with the URL and pre-filter the requested report. In our case, the URLs need to be dynamic because the filter, which will be a query string parameter, depends on the value the user has clicked on.

Let’s see how to do this:

1. Create a column for the URL

Each state should have an URL with a static part that contains workspace, target report and page id. Also, this URL needs the query string parameter and the filter operator. Because we are filtering states, we append Cities/state_name eq at the end of the string, using the equal (eq) filter operator. At last, we include the selected state: States[state_name], this will be the dynamic part of the URL:

‍

This calculated column we’ll be eventually translated into a specific URL for each row of our States table:

‍

2. Format the column as Web URL

Now we need to conditional format the State Name column so it can be transformed into a Web URL:

‍

Then, we base our formatting on the value of our recently created State URL column:

Our report is now ready, the State Name column has a link to the target report filtered by the selected state:

‍

3. Upload reports to the Power BI service

Once the reports are on your workspace and you have followed the configuration steps needed, you can start our sample web application and see them listed on the left:

‍

‍

Updating the slicers

If we test our work in progress right now, we’ll see that even though we are intending to filter cities by Iowa the target report lists cities from Alaska:

‍

‍

This has to do with the slicer we have on top of the table. As we mentioned previously, slicers won’t be affected by query string parameters we append on the URL. A quick workaround will be to remove this slicer, but we want to keep it so the users can continue filtering by other states if they want.

In order to fix this, our sample app makes use of the Power BI Javascript SDK to update all the slicers that are based on the field we passed on the URL, in this case State Name.

With this feature in place, we can see how the target report is now filtered by the selected state, Iowa:

‍

‍

Extra functionality

We can also go a step further and extend this slicer-updater feature so it can propagate selected filters while navigating not necessarily through hyperlinks on embedded reports but reports listed on the side panel.

‍

This is an option available on the right top of our sample. The user can select on Preserve Filters, which means that all the slicers on the current report will be propagated to the next one, if available.

‍

Conclusion

Power BI Embedded is a powerful tool that in combination with its SDKs, empowers the features and functionalities that our web application can have.

You can find the source code of this sample application here.

‍

Originally published by Hernan Demczuk for SOUTHWORKS on Medium 28 December 2020