Business users’ most common requirement is to view data in two different visuals – mostly as Chart and Table. With standard configurations in Power BI Desktop, we don’t have the option to do this, but can be attained using Bookmark and Selection Pane.
Bookmark and Selection Pane
Bookmark is one of the most requested features in the Power BI Desktop. It’s been available as a preview feature since the October 2017 version. It allows the users to save the current state and share insights with other users.
Selection Pane is also available as a preview feature since the October 2017 version. It allows us to control the visuals like moving forward/backward and show/hide.
It may sound like any other feature, but when we combine both features, we will be able to create some really interesting reports.
Consider a data set which has sales values by region, state and city.
Here is the layout of a sample report,
The chart on the top left shows sales amount for all the cities. It has 20+ cities, so some users may feel that a table would help gain insights from the data and others may prefer a chart. Ideally, the developer/analyst has to provide both the visualizations in the same area. But the challenge here is that the functionality is not available on Power BI Desktop.
Here’s how we can achieve this requirement of toggling between chart and table.
Assuming we have a report like the one above,
Step 1 – Add the required table and the chart one over the other in the same canvas area.
In this post both the chart and the table show ‘Sales Amount by City’.
Step 2 – Add two images/icons
These two images will be linked with the bookmarks.
Step 3 – Enable the Bookmark Pane and Selection Pane
You get two panes on the right side
Step 4 – Create two bookmarks
One is to show the chart
Hide (by clicking the ‘eye’ icon) the chart image and the table. Save it as a bookmark by clicking on Add.
Another one is to show the table.
Similarly, hide the table image and the chart. Save it as a bookmark.
Step 5 – Now link the images with the bookmarks,
This is the final step. Click the table image and we have the option to link to the bookmarks. Select ‘Bookmark’ from the dropdown. Once the selection is made, another dropdown(Bookmark) appears – which shows the saved bookmarks. Choose the ‘Table’ bookmark as shown in the image below.
Similarly, for the chart image select the ‘Chart’ bookmark as shown below,
With this, we have created a workaround for the toggle functionality. Similarly, we can have multiple toggle options for periods like MTD, QTD, and YTD in Live connectivity mode.
Got questions? Click here to reach out.