The Baltimore City Open Data Hub "invites you to interact with the data by using our dashboards, download content, analyze data, or build apps using our APIs."
Using this data source, I created an interactive tool that allows exploring 3 categories of Baltimore 311 service requests: Solid Waste, Water Wastewater, and Public Works.
It shows:
When requests were received (trends)
What requests were received (most common types of service requests)
Where requests were from (council districts and neighborhoods)
For example, we can confirm that requests had a huge drop after COVID-19 was declared a pandemic and our lives changed:
Also, we can see that Baltimore 311 differs from 311 operations in some other cities because its open data includes work that is proactive. We can confirm that such data comes from internal systems, rather than phone/app/web requests (which typically are for problems that requires a reaction, rather than a preventive approach):
And we can learn about requests in different neighborhoods:
Making the Dashboard
Note: Microsoft calls this a report; dashboard has a different meaning. I'll be using the term dashboard, not based on its technical meaning.
Microsoft has an amazing amount of free training material - including interactive labs - available at its Microsoft Learn website.
RADACAD's Power BI Summit provided an incredible amount of inspiring tips and tricks, as well as announcements from Microsoft about the product direction. I learned a lot from speakers such as Matt Allington, Reid Havens, Ana Maria Bisbe York, Nikola Ilic, and Christian Borovac.
The Baltimore Open Data provided an opportunity to try out some of what I had learned, including:
Providing a Clear Filters button
Power BI dashboards are intuitive. Clicking on something often changes something else. This allows filtering of, for example, what shows on a chart based on clicking on a date range.
Sometimes, it is less intuitive to know how to reset the filters. Using a button and bookmark, a "Clear Filters" option makes it easier to do this:
Buttons to choose amount of detail
A simple button allows the user to choose whether to see more or less information. (Behind the scenes, this uses buttons and bookmarks.)
Dynamic Tooltips
When the user hovers over a part of a chart, matrix, etc. additional information about the filtered item can display. This is handy for providing an additional level of detail that might be interesting at a glance, but doesn't need to take up space on the main page. It also means that the user can see this information without being taken to another page. (Here is how it is done.)
Maps
The open data source already included Latitude and Longitude, so it was a simple matter to show the location of requests based on the built-in Map visualization:
Time Intelligence (Year over Year)
Power BI's time intelligence measures allow, for example, showing the change year over year. The same measure can be used regardless of what filters are applied. By creating one measure, you can show the change for all requests from April 2020 compared with April 2019; or for only requests in a particular council district and for a different month.
Doing this requires a date table, and happily a great DAX Date Template is offered by sqlbi, which saves a lot of work.
Even so, I had trouble using it, until I duplicated a Date/Time column in Power Query and changed it to be a Date column.
Other notes and nice things
It has page navigation buttons
It uses synced slicers (so that if you choose, for example, to see only requests from 2020 on the What page, that preference is carried over to the Where page)
There's a background image (it's 95% transparent except on the Information page, so it's very subtle)
Data transformation involved combining multiple files (Baltimore Open Data provides a download for each calendar year). At download time, the 2021 data had a different schema (additional columns), which required some cleanup. Also, there were a few requests with incorrect latitude/longitude. So I learned that Power Query requires null to be in lower case, as in: Table.ReplaceValue(#"Replaced Value2",-76,null,Replacer.ReplaceValue,{"Longitude"})
For the time intelligence calculations, IsBlank and BLANK() are helpful so that visuals do not show clutter when there is nothing to calculate: Qty SR YoY% = if (IsBlank ([Qty SR]), BLANK(), VAR __PREV_YEAR = CALCULATE([Qty SR], DATEADD('Date'[Date], -1, YEAR)) RETURN DIVIDE([Qty SR] - __PREV_YEAR, __PREV_YEAR) )