SOUTHWORKS Dev Team
December 28, 2020
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:
In our sample, we’ll be tackling all these inconveniences.
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:
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:
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:
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:
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.
With this feature in place, we can see how the target report is now filtered by the selected state, Iowa:
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.
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