1. Increasing report interaction
Welcome to the first demo. Let’s start by taking a quick peek at our dataset, which will be quite different from the one you will use in your exercises. On the Model view, we can see the basic structure of our data model.
Our primary fact table is Food Inspections, which has the date and score for each restaurant inspection. Scores range from 0 to 100, with 100 being the best. Restaurant details are available to us in a restaurant dimension, and the key between restaurants and inspections is the restaurant’s HSIS ID. We also have food inspection violations, which include a description of the violation, text comments, and a numeric value representing how many points were lost due to this violation. Severe violations, such as employees not washing their hands before touching food, lose more points than less severe violations. An example of a less severe violation would be a dirty floor. Finally, we have date dimensions that tie to critical inspection, violation, and restaurant open dates.
Next, let’s create a couple of DAX measures. First, on Food Inspections, let’s add a measure called Number of Inspections, defined as the count of inspection keys. That way, when we filter on some criteria, we’ll see the number of inspections performed given those criteria.
On the violations table, we’ll create two measures, one which gives us the number of violations and another which sums up the number of points lost. These will perform a similar purpose but for inspection violations.
Let’s rename the current page to Overview by right-clicking the page tab and selecting rename page. We’ll start with a simple overview page with a clustered bar chart that includes the number of inspections by the inspector.
Let’s add a table visual that includes details on a given inspection. We’ll pick the inspection date as an integer key, the restaurant’s HSIS ID, the restaurant’s name, and the number of violations found on that day.
That bar chart is a little tight, so let’s give it some room by removing the Y-axis title. To do that, navigate to the format, drill into the Y axis, and turn off the title. It’s still a tight fit, so we will select the table and right-click on Number of Violations, renaming it just for this visual to Violations.
This overview page is acceptable for now, so next up, let’s create a new page called Inspection Report. This will take a drill-through field: inspection key. Note that adding a drill-through field automatically creates a back button for us. We’ll talk a bit more about buttons in later videos.
It might be nice to see where in town a given restaurant is, so let’s add a map visual. We’ll connect the Restaurant’s Y value with the latitude and X with the longitude. It’d also be nice to see a bar chart with the violation types. We’ll use the violation code for our axis, the number of violations for values, and include the short description as a tooltip, as it’s unlikely the general public will know what these North Carolina statutes mean.
Let’s return to the Overview tab. To effectuate the drill-down, we will need to add the drill-down filter column to one of our visuals, and given that the table is already at the inspection level, it makes sense to add it there. After doing so, we can right-click on a row, navigate to the Drill through on the menu, and select the Inspection Report.
This shows us exactly where the restaurant is and the recorded violations.
Let’s return to the Overview page and add a scatter chart. We will include the number of inspections on the X axis, the average inspection score on the Y axis, the facility type as the Legend, and the count of HSIS IDs from the food inspections table as the size.
This gives us an idea of the overall score by category, with public school lunchrooms rating highest. To see this change over time, let’s bring the inspection date’s year and quarter as the Play Axis. Now we get a dynamic view of how things have changed over time. Let’s also change the format of the Y axis to start at 90 and end at 101, as inspection scores rarely dip below that value.
Now we have the beginnings of a report. There’s still a lot of work left to do, but take these skills with you as you do the exercises.
2. Let's practice!