In the next couple of posts I’ll be focussing on some of the work I’ve been doing for a London Borough since July last year. 

The Council was keen to provide a snapshot of the issues affecting different wards in the borough, and asked me to design a report that could be used face-to-face with councillors and members of the public at ward forums. I came up with a simple structure for a Power BI report with a front page that could be used to navigate to different report pages:

The report always displays data up to the end of the last completed financial quarter, in this case Q3 2020/21, as displayed in the report title. The report user selects the ward they are interested in (a ward is a geographical and administrative subdivision of a local authority in the United Kingdom, represented by one or more democratically-elected councillors). This filters the remaining pages in the report to display data for the selected ward. I’ve used the ChicletSlicer visual from Microsoft instead of the built-in Power BI slicer for this area of the report as it provides greater flexibility in formatting than the default.

My report also uses colour-coding to provide a visual clue to the user about the scope of the data they are viewing. Visual elements in maroon indicate a Borough-level measure, whereas green indicates a ward-level measures. The report provides information about the four biggest issues reported by residents: fly-tipping, highways issues, noise nuisance and anti-social behaviour. In addition, there are further pages showing information about street inspections carried out by Council officers and any enforcement activities they may have undertaken.

The report proper starts with an overview of neighbourhoods issues for the selected ward:

The first thing to notice here is that the title of the page automatically includes the name of the ward selected on the front page. I’ve achieved this using a measure to calculate the ward name, then display the result of the measure in a multi-row card visual, formatted to display the title left-justified, against a dark blue background. Here’s the code for the measure:

«Label» Neighbourhoods Borough = 
"Neighbourhoods issues | " & [«Label» Ward]

«Label» Ward = 
SELECTEDVALUE ( Wards[Ward], "Multiple wards" )

The measure concatenates the selected ward from the Wards table with a fixed text label. In the event that the report user selected multiple wards from the front page, the measure will return the value “Neighbourhoods issues | Multiple wards”.

The five visuals at the top of the report are actually each composed of three card visuals:

The top card, in the dark green box, displays the result of a constant measure. Using a card visual ensures that the text in the box is precisely centre-aligned both horizontally and vertically:

«Label» Last Quarter = 
"Last quarter"

The middle card, in the pale green box, contains a measure that computes the most recently-completed financial quarter, based on the Date table in the data model:

Last Completed Quarter = 
LOOKUPVALUE (
    'Date'[Financial Quarter Year],

    -- The current, in-progress quarter has a relative quarter position of 0
    'Date'[Financial RelativeQuarterPos], 1
)

Finally, the bottom card contains the actual measure itself, which in this case computes the total number of service requests received in the ward in the last completed quarter:

Number of Cases LQ = 
VAR _LastQtr = [Last Completed YearQuarterNumber]
VAR _Result =
    CALCULATE (
        [Number of Cases],
        'Date'[Financial YearQuarterNumber] = _LastQtr
    )
RETURN
    _Result


Last Completed YearQuarterNumber = 
VAR _Today =
    TODAY ()
VAR _CurrentQtr =
    LOOKUPVALUE ( 'Date'[Financial YearQuarterNumber], 'Date'[Date], _Today )
VAR _LastQtr =
    _CurrentQtr - 1
RETURN
    _LastQtr

The cards that show a change in a value use conditional formatting to display adverse changes in red, and positive changes in green:

The line chart on the right of the page is interesting:

Here, I’m plotting three measures: one showing the values for the ward, one for the Borough as a whole, and the final one showing the region that the ward is part of, as the Council is divided into a number of regions, each of which comprises several wards. It’s not possible to create a dynamic legend for a chart in Power BI Desktop, so I’ve created measures to calculate the ward and region names, then used these measures in card visuals to simulate a legend that changes when a different ward is selected.

The final page I want to share here contains a table visual that ranks the currently selected ward against all the other wards in the Borough:

Here, the selected ward is highlighted in a buff colour against the other wards, listed in descending order by number of service requests reported. I first tried to implement this table using the default data model I designed for the report:

However, I found that when I applied a ward filter using the slicer on the front page, this resulted in a table visual that contained only the filtered wards: the remaining wards in the Borough were no longer included:

I needed to find a way of preserving the other wards in the table so that I could rank the selected ward against them. I did this by creating an unrelated table called Service Requests Borough Context, purely for the purpose of aggregating the number of service requests for each ward. Because this table was not related to the Ward dimension, filtering on ward would mean that any visual based on the unrelated table would continue to include all wards, even when a filter on ward was applied to the report as a whole:

I created this unrelated table in Power Query as part of data load. Here’s the code:

let
  Source = ServiceRequests,
  RemovedOtherColumns = Table.SelectColumns(
    Source,
    {"Ward", "Report Type", "Reference", "ReportedDateKey"}
  ),
  RemovedDuplicates = Table.Distinct(RemovedOtherColumns),
  RemovedColumns1 = Table.RemoveColumns(RemovedDuplicates, {"Reference"}),
  MergedQueries = Table.NestedJoin(
    RemovedColumns1,
    {"ReportedDateKey"},
    Date,
    {"DateKey"},
    "Date",
    JoinKind.LeftOuter
  ),
  ExpandedDate = Table.ExpandTableColumn(
    MergedQueries,
    "Date",
    {"Calendar RelativeQuarterPos"},
    {"Date.Calendar RelativeQuarterPos"}
  ),
  FilteredRows = Table.SelectRows(
    ExpandedDate,
    each [Date.Calendar RelativeQuarterPos]
      >= 1 and [Date.Calendar RelativeQuarterPos]
      <= 4
  ),
  RemovedColumns2 = Table.RemoveColumns(
    FilteredRows,
    {"ReportedDateKey", "Date.Calendar RelativeQuarterPos"}
  ),
  GroupedRows = Table.Group(
    RemovedColumns2,
    {"Ward", "Report Type"},
    {{"Cases", each Table.RowCount(_), Int64.Type}}
  )
in
  GroupedRows

The final step is to add a measure in DAX that determines whether the current row in the unrelated table corresponds to the currently selected ward on the front page:

SR Formatting = 
VAR _CurrentWard =
    SELECTEDVALUE ( Wards[Ward] )
VAR _Result =
    IF ( SELECTEDVALUE ( 'Service Requests Borough Context'[Ward] ) = _CurrentWard, 1, 0 )
RETURN
    _Result

All that remains now is to add some conditional formatting to the visual containing the list of wards, based on the value of the SR Formatting measure:

The row that corresponds to the currently filtered ward will be highlighted with a different background colour.