Welcome back to Workout Wednesday – we’re glad you’re here! This week, we’re continuing on with our project management theme. We’re tracking project progress using data generated from Microsoft Planner. Using Power BI with Planner (as opposed to using the inbuilt charts) enables us to both customize visuals and to view more than one plan at a time.
The challenge this week is to build a slicer panel in a report, using the provided planner data. Slicer panels enable us to make use of all the real estate of the canvas. They are an alternative to using the inbuilt filter pane, which may feel less intuitive to report consumers. Additionally, the inbuilt filter pane isn’t visible if a report is shared publicly using publish to web.
Side note: if you’re using Planner and are interested in automating your reporting, Ben Howard has a great video explaining the end-to-end process.
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:
- 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:
- Due date (relative date)
- 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.
This week’s data is generated by me, planning a fictitious project. You can access the data in Excel if you’d like to complete the data modeling in Power BI. If you would prefer to work with the data already modeled, use the .pbix file to jump straight into the data viz.
After you finish your workout, share on Twitter using the hashtags #WOW2022 and #PowerBI, and tag @JSBaucke, @MMarie, @shan_gsd, @KerryKolosko, and @NerdyWithData. Also make sure to fill out the Submission Tracker so that we can count you as a participant this week in order to track our participation throughout the year.
Download the completed Power BI desktop file.
There is no solution video this week because there’s already an incredible resource online. Check out Guy in a Cube’s video on Next Level Slicer Panels.