In Weekend Work 010 we looked at using Calculated Columns in DAX to prepare a KPI, it’s not uncommon to have Calculated Columns created as you go through the non-stop development process of your Data Model (remember there is no real endpoint to a data solution). A great skill to pick up is being able to updated and move elements around the model, in this case we are taking data back into the system so the impact on the viewer is minimal as the whole thing has been processed before it hits the Power BI Service.
In the past we have unpivoted our data within Query Editor, to complete what we’re doing this time we need do the opposite, this time we will use “Group By”
New York City requires that Drinking Water Rooftop Storage Tanks are inspected annually, the results are then published, so we can use that published data to see who is up to date with their inspections. Remember as we’re going to set this as our KPI, our “Good” value is going to be interesting because it is January, so it would be expected that the majority of sites would be in an “Awaiting Inspection” state. In practical terms for the City or Inspection Companies they could use that to contact Tank owners and look at next steps or look into why inspections have been missed. In the spirit of transparancy, remember that if sites have been closed due to Covid-19 it may explain the high numbers that were not inspected last year. You may also see that there seems to have been a change in regulations in November 2019, considering there seem to have been more inspections than ever last year (despite Covid-19) it may well be that Capacity is becoming a factor. Let me know what you think?