Power BI: Sydney’s Register of Food Penalty Notices – Part 4
21 November 2019
Welcome back to this week’s Power BI blog series. This week, we are going to look at producing some customised categories for the NSW Food Authority’s register of penalty notices data.
Last week, we were able to accurately plot the different addresses of the offenders on the Map visualisations in Power BI.
This week, let’s take a look at what else can we derive from the given information.
Looking at the Nature & circumstances of alleged offence field, we notice that there is a key metric that is not recognised in the original data: whether or not the offender is a repeat or first-time offender.
From the excerpt of the data above, we can tell that a business is a repeat offender upon the second time, if “previous warning given” is present in the row. We can also tell that a business has been warned more than two times if “previous warning(s) given” is present in the row description. Furthermore, we are going to assume that a business is a first-time offender if there is no indication given. With this in mind, let’s create our custom category.
We create a custom category by using a custom column in DAX (you can read more about creating custom categories with DAX in our Power Pivot blog here). We head over to the Data tab, then create a new column with the following code:
No. of Offences =
"previous warnings given",[Nature & circumstances of alleged offence],,0)>0,
"3rd >= Offence",
"previous warning given", [Nature & circumstances of alleged offence],,0)>0,
We can now use the custom column as a filter, so that our users can find restaurants that appear to be repeat offenders.
That’s it for this week! Check back in seven days for more analysis on this dataset! Until then, happy eating!