2022 Week 12 | Power BI: Build a Slicer Panel

If you’re using Planner data from Excel, start with:

• Connect to Excel online and bring in task data
• Create a date table – this can be done in Power Query using M or in the front end of Power BI using DAX
• Create a dimension table for Priority – you will use this to enforce a sort order
• Create relationships between your task table and:
  • Date
  • Priority
• Create measures for: number of tasks, number of tasks completed, number of tasks in progress
• Note that you can build this report without creating the date and priority tables, but it won’t behave exactly as you’d like.

If you’re starting with the .pbix file with data already loaded/modeled:

• Create your report showing project progress – use any combination of visuals that work to give an overview of project status. I used:
  • 3 card visuals to show total tasks, tasks in progress, and tasks not started
  • A pie chart to show the proportion of tasks by progress (not started, in progress, completed)
  • A gauge displaying percent complete
  • A stacked bar chart displaying the number of tasks assigned to each person, by project (this is the “bucket” column in Planner)
• Build your slicer panel
  • Start by adding a shape to the canvas where you want the slicer panel to appear
  • Add any combination of slicers that would be useful to monitor this project. I used:
    • Priority
    • Due date (relative date)
    • Progress
    • Overdue
  • Format your slicers. Most of my slicers are multi-select lists. I also turn of the visual header of the slicers to avoid busy-ness.
  • Add a shape that will be used as the button to minimize your slicer panel. I used a pentagon arrow.
  • If you want to get fancy, add a button to clear all slicers. I find this particularly useful so that end-users don’t have to de-select every single little item.
• Let the fun begin with bookmarks! From the view tab of the ribbon, turn on the bookmarks and selection pane.
  • Create two bookmarks, one for when the slicer panel is expanded/showing, and another for when the full report canvas is showing. Name them accordingly.
  • If you created a clear filters button, create a third bookmark for that.
  • Use the selection pane to assign a different view to each of your bookmarks. Each item can be shown and hidden in the selection pane. 
  • Assign your bookmarks one at a time – be sure to update each bookmark (by using the ellipsis menu) before moving on to the next.
  • You must de-select Data from the ellipsis menu to ensure that your slicer selections remain even as you toggle between bookmarks.
  • Once your bookmarks are behaving as you’d expect, add the shape to show the slicer panel.
  • Assign an action to each of your shapes using your defined bookmarks.
• Check out the resources tab of the Power BI report for videos on building a slicer panel and automating the use of Planner with Power BI.