Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Pragati11

Drill-up Drill-down Capability Using Bookmarks in Power BI

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]

i1.png

 

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:

gif_hierxrc.gif

 

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:

i2.png

 

The setting for the YEAR bookmark is shown below:

i3.png

 

Follow similar steps to create QUARTER and MONTH bookmarks and we end up having following on the report page:

i4.png

 

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, 

i5.png

 

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):

i6.png

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.

gif_bookm.gif

 

Once all the bookmarks are created, we need to configure them for the view we want them to show.

i7.png

Main points to note here:

  1. We will update "Year" bookmark on the right, when the matrix is showing YEAR hierarchy view.
  2. We want this Year level data to be seen when we use the "Year" bookmark.
  3. 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.

i8.png

 

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.

i9.pngDo the same for "Month" bookmark as well. See below:

i10.png

 

Now set-up action for QUARTER and MONTH buttons by pointing them to respective bookmarks from the Bookmark Pane:

i11.png

 

i12.png

 

Now we are all set to use the Buttons on the report.

gif_book_final.gif

 

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

 

Comments