Power BI · Power BI Desktop

Dynamically Changing A Chart Axis In Power BI Using Bookmarks And Buttons

A very common requirement when building Power BI reports is to allow the end user to change what is displayed on a chart axis dynamically. A lot of people have blogged about how to do this – Kasper’s blog post here is a great example – but the problem is that all of these solutions involve a lot of work remodelling your data and writing DAX code. However, the good news is that now we have Bookmarks and Buttons in Power BI there’s a new, easy, code-free way of achieving the same result, at least for some chart types. In this post I’ll show you how using the same data that Kasper used in his post.

Say you have the following dataset (using data from the Adventure Works DW sample database) in Power BI Desktop:


…and you need to display a column chart that shows the sum of SalesAmount broken down by either Country, Region or Currency.

The first step is to create a column chart and to drag Country, Region and Currency into the Axis well:

At this point the column chart will show Country and you’ll have the option to drill down – but don’t drill down yet. Add a Bookmark at this point and call it Country. Do not turn on drill down mode, but click on the “Go to the next level in the hierarchy” button:

When you do this, Country will be completely replaced by Region:

Add another Bookmark called Region, then click “Go to the next level in the hierarchy” again to show Currency:

Add a third and final Bookmark and call this one Currency. At this point you should have three Bookmarks for the three drill states:

The last thing to do is to add three buttons to the report linked to the three Bookmarks:

In this case I’ve used the “blank” button type, turned on the Outline, added button text that matches the name of the Bookmark, and set the Action Type property to “Bookmark” and then selected the appropriate Bookmark in the Bookmark property. Here’s how my Country button is configured:

And that’s it. After the report is published (notice that I’ve also used the new ability to turn off the visual header which makes everything look much tidier) you’ll be able to click the three buttons and switch between viewing Country, Region and Currency like so:

Of course this only approach works with visuals like the column chart that support drilldown so you can’t use it in all cases, but it does show off how powerful and useful the Button/Bookmark combination is. Ideally the Selection Pane would be able to control the visibility not just of entire visualisations but also of the fields and measures used within a visualisation, which would enable even more scenarios like this.

You can download the sample .pbix file for this post here.

6 thoughts on “Dynamically Changing A Chart Axis In Power BI Using Bookmarks And Buttons

  1. Hi Chris.

    Thanks for the post.

    A combination of bookmarks and buttons is in fact powerful. But there is a drawback of using it – the maintenance and further development needs to be done with caution and requires serious additional effort, because usually you have to update bookmarks whenever any new visual shows up on the report page. The “View” option in Bookmarks pane becomes really helpful when you’re lost in what’s on which bookmark actually.

    The method presented in your post is definitely easier way for Power BI users to dynamically change axis than any method requiring DAX skills.


    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      I didn’t need to do anything – the title changes automatically when you drill down/up.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.