The Power BI Visualization

Our SharePoint list is an easy-to-use data source. Figure 10-1 shows the results a couple of weeks after going live with this solution.¹

A screenshot of tickets from the last 30 days in Share Point.

Configuring Power BI

As with earlier Power BI solutions, much of the work is in transforming the query. Our first challenge is to get the display name of the User (Office 365) People column. As shown in Figure 10-2, we expand the FieldValuesAsText and select that column.

A screenshot of field values as text with several options at the top and field values as text with names at the bottom.

We rename that column to be Office 365 User. We then create a conditional User column that will have this Office 365 User value if has a value and otherwise contains User (Not Office 365). See Figure 10-3.

A screenshot of Add Conditional Column with several options along with O K and cancel in the bottom right.

If the status is Resolved, then we make sure the Resolved Date is greater than the Created date. If so, the value is the difference. If not, we give it a value of 0.² If the ticket has not yet been resolved, we take the current date and time and subtract Created from that. We then change this type to be a whole number. We use this value in the Open Tickets > 7 Days page as well as displaying as a Tooltip on all graphs.

This allows us to see all tickets created during a time period. As you might guess, this is what we use for the Tickets Last 30 Days and Tickets Last 90 Days pages back in Figure 10-1.

Our last main change is to rename Id to Ticket #. This keeps us from having to rename it for each visualization.

Page-Level Filters

Much of the work for the visualization is in the page-level filters. As we see in Figure 10-4, we limit the Open Tickets page to tickets where the Status is not Resolved. Similarly, the Tickets Last 30 Days page has a single filter for Days Since Created <= 30. We want to include resolved tickets in this case as we want to see the entire workload.

A screenshot of the visualization of the two page-level filters with status and days since options.

For the Open Tickets > 7 Days page, however, we need two filters: Days Open > 7 and Status is not Resolved. Power BI used to only allow one page-level filter, but now we can add multiple ones. This is a nice enhancement and avoids us having to add the second one to each visualization on the page. Figure 10-5 shows the two filters.

A screenshot of the visualization of the page-level filters with days open and status options.

As in past examples, our pie charts have the data we are tracking (Status or Assigned Technician) as the Legend. We then have the Count of the ID/Ticket # as the Values. We rename this to # of Tickets to make the labels more understandable. We show Average of Days Open as the Tooltip.

The stacked column chart (Figure 10-6) gives us additional information. We show the Assigned Technician along the horizontal axis. The Legend is the Status. These are each in a different color. The Value is once again the Count of ID/Ticket # that we rename to # of Tickets. We display Average of Days Open as the Tooltip. As you can see in Figure 10-6, all of these values show up as the tooltip when we move our mouse over one of the segments of the bar chart. It also shows the Table control showing the detailed information.

A screenshot of a bar chart at the top and a blurred box at the bottom. Arrows point from the box of visualizations with several options to the bar chart and other regions.

Summary

In this chapter, we learned how to visualize our SharePoint ticket data to get insight into the amount time tickets were open, the ticket mix per technician, the workload, and more. Custom columns like Days Open and Days Since Created helped us filter the pages to focus on open tickets and workload and show important statistics like the average number of days a ticket was open.