Toggle between a chart and a table

by SivaMani Senior Member on ‎11-28-2017 04:24 AM - last edited on ‎12-01-2017 10:47 AM by MindyJ Frequent Visitor

One of the most requested requirements from the Business users is to view the data in two different visuals, usually in a chart and table. With standard configurations in Power BI Desktop, we don't have the option to do this. But, we can get this by using the Bookmark and Selection Pane. Shown below is an example of what this looks like.

 

Toggle.gif

 

Bookmark and Selection Pane:

Bookmark is one of the most requested features in the Power BI Desktop. This feature has been available as a preview feature since the October 2017 version. Users can save the current state and share insights with other users.

 

Selection Pane has been 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 other features but if we combine both features, we will be able to create some interesting reports.  Let’s get started!

 

Requirement:

Find a dataset which has sales values by region, state, and city.

 

SampleData.PNG

 

Shown below is the layout of a sample report.

 

Layout.png

 

The chart on the top left shows sales amount for all the cities. It has 20+ cities so some user feels that a table would give the best data insights and other users may prefer a chart. Ideally, the developer/analyst provides both visualizations in the same area. But the challenge here is that the functionality is not available on Power BI Desktop.

 

Below are the steps to toggle between a chart and table.  The assumption is that 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 table are showing sales amount by city.

 

Step 2 – Add two images/icons.  These two images will be linked with the bookmarks. 

Images.PNG 

 

Step 3 – Select the Bookmarks Pane and Selection Pane on the View tab.

 

EnablePanes.PNG

 

You will get two panes on the right side.

 

Panes.PNG

 

Step 4 – Create two bookmarks.  
The first bookmark shows the chart.  Hide the chart image by clicking the ‘eye’ icon. Save it as a bookmark by clicking the Add button.

 

BkmTable.PNG

 

The second bookmark shows the table.  Hide the table image and the table by clicking the ‘eye’ icon. Save it as a bookmark.

 

 BkmChart.PNG

 

Step 5 – Link the images to the bookmarks. 

This is the final step.  Click the table image and you have the option to link to the bookmarks. Select Bookmark from the dropdown list. Once the selection is made another drop-down (Bookmark) appears.  This shows the saved bookmarks. Choose the Table bookmark as shown in the image below.

 

 LinkChart.PNG

 

Similarly, for the chart image select the Chart bookmark as shown below.

 

LinkTable.PNG

 

With the steps above 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. However, the filter selections cannot be retained in the current version since the Bookmark functionality is available only as a preview mode. I expect this feature to be available in the future releases.

Comments
by SivaMani Senior Member
on ‎12-13-2017 07:50 AM

Update:

Now we can retain the filter selections using the latest feature of Bookmark functionality.

Refer: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-december-feature-summary/#bookmarkFlexibil...

by Samrawit21 Regular Visitor
on ‎09-30-2018 11:26 PM

Where can I find the toggle images? 

by SivaMani Senior Member
on ‎09-30-2018 11:36 PM

@Samrawit21,

 

You can get the icons here.