Power BI has got an amazing functionality to change the hierarchy of the hierarchical data using DRILL-DOWN and DRILL-UP icons provided on the visuals.
We can create a different view for the similar functionality which may look better from user's perspective.
In my following article, I will take you through the steps of creating a drill-action on the hierarchical data by using Bookmarks.
Let us consider a simple matrix visual which shows the following information:
[It shows total sales by different category by date]
In Power BI, we can easily use the default drill-up and drill-down icons to see the data at different DATE granularities.
See below:
Now let us see how we can implement the above functionality using Bookmarks.
We will start by creating 3 bookmarks for YEAR, QUARTER and MONTH.
See below for the steps:
The setting for the YEAR bookmark is shown below:
Follow similar steps to create QUARTER and MONTH bookmarks and we end up having following on the report page:
In order to get the functionality working, we will have to create actions for these bookmark buttons on the report page.
For this we will start by enabling the BOOKMARKS PANE,
Once you select this option, you will see a Bookmarks Pane on the right which shows all the existing bookmarks in your report (if there are any):
In my case, I already have 2 existing bookmarks on other tabs of my report which are shown here in this pane.
Now we start with creating 3 bookmarks in this pane, which we will use to create actions for our buttons on the report page.
Once all the bookmarks are created, we need to configure them for the view we want them to show.
Main points to note here:
- We will update "Year" bookmark on the right, when the matrix is showing YEAR hierarchy view.
- We want this Year level data to be seen when we use the "Year" bookmark.
- We will do the same for "Quarter" & "Month" bookmarks as well under the Bookmarks pane by updating them at their respective granularity.
Once the bookmark is updated under Bookmark's Pane for "Year", we click on our YEAR button and set up an action to it by pointing it to the "Year" bookmark.
We will follow the same steps for QUARTER and MONTH buttons on the reporting page.
Use the default drill-down option on matrix visual to go to Quarterly hierarchy and then update the "Quarter" bookmark under the pane.
Do the same for "Month" bookmark as well. See below:
Now set-up action for QUARTER and MONTH buttons by pointing them to respective bookmarks from the Bookmark Pane:
Now we are all set to use the Buttons on the report.
Voila! We have successfully implemented Drill-Up and Drill-Down capability on a matrix visual using Bookmarks. This can be easily used on other visuals as well. Also, the granularities can be based on different data-elements like Country-->State-->City, etc.
Hope this helps everyone out there.
-Pragati
Super User
