In my last post I described how I’ve helped a London Borough to visualise neighbourhoods issues for elected councillors and members of the public. One of the areas covered in outline in this report was anti-social behaviour (ASB). Following implementation of the neighbourhoods report I was asked to develop a more detailed report on ASB for the Council. This post describes how to use Tabular Editor and calculation groups to simplify the application of similar measures to a set of base measures.
The neighbourhoods report was focussed on reporting measures for the last completed financial quarter only, so it was comparatively easy to develop the limited number of measures in DAX to provide this functionality. However, for ASB the requirement was more sophisticated. The report needed to be capable of showing the number of ASB incidents between the start of any financial year and any subsequent date in the financial year, with comparatives for the same period in the previous financial year. Furthermore, the report required a number of base measures, such as:
- number of incidents reported
- number of open incidents
- number of incidents open for more than 20 working days
- average number of working days for which incidents were open
Rather than hard-coding different time intelligence calculations for each of these base measures, which would make the number of measures in the report very large, and would also be more difficult to maintain, I decided to use a calculation group to deploy time intelligence in the report. A calculation group would allow me to use filtering in the report to control the specific time intelligence calculation to apply to a base measure. By including the base measures in a second calculation group, I could even use filtering in the report to control which measure I wanted to apply a time intelligence calculation to.
Let’s look at an example. Here’s the first page of the report I designed:
As you can see, the report page includes a number of card visuals displaying different measures for different time periods. it also includes a couple of column charts and a bar chart, and there is a date slicer which allows the user to select the time period over which to report. In the example shown, the time period is 1st April 2020 to 13th December 2020 (the financial year to date) with comparatives shown for the corresponding period in the previous financial year.
Let’s take the Cases closed card as an example. The base measure used in this card is called # Cases Closed and is defined as follows:
# Cases Closed = CALCULATE ( COUNTROWS ( 'Case Granularity' ), USERELATIONSHIP ( 'Case Granularity'[CaseClosedDateKey], 'Date'[DateKey] ) )
As you can see, the measure is simply counting the number of cases in a fact table with granularity at the case level, using a relationship with the date dimension table that defines the date on which the case was closed. However, the measure contains nothing to ensure that the calculation performed gives us the total number of cases closed for the current financial year to date. An examination of the filter panel for the card visual reveals that a filter is being applied to the card on a Field called Measure from the table Time Intelligence:
What is actually happening here? A glance at the Data view of the Time Intelligence table in Power BI Desktop indicates that this table has two columns, one that seems to contain names of measures, and the other containing a number, but it doesn’t seem to be possible to get any further insight on what this means within Power BI Desktop:
In fact, the Time Intelligence table represents a Power BI concept (or more correctly, an Analysis Service concept) called Calculation Groups. There is no user interface in Power BI Desktop to create or manage a Calculation Group, but it is possible to do this using an external tool, Tabular Editor. Once Tabular Editor is installed, it will show up automatically in the External Tools ribbon in Power BI Desktop:
This will launch the Tabular Editor app and connect automatically to the underlying Analysis Services database instance that supports the report in Power BI Desktop. Expanding the Tables section of the model reveals that there is a table called Time Intelligence which defines a Calculation Group. The properties of the group appear in the panel to the right:
The Calculation Items in the group correspond to the rows in the Time Intelligence table visible in Power BI Desktop. However, in Tabular Editor it is possible to manipulate the properties associated with each Calculation Item. Selecting Financial Year To Date from the list of Calculation Items reveals the following set of properties, as well as some DAX code associated with the item:
The measure expression determines the start date of the financial year in which falls the date selected in the slicer on the first page of the report (the As At Date), which in turn is calculated as follows:
As At Date := VAR _IsDateFiltered = ISFILTERED ( 'Selected Date' ) VAR _Filtered = CALCULATE ( LASTDATE ( 'Selected Date'[Date] ), ALLSELECTED ( 'Selected Date' ) ) VAR _Unfiltered = TODAY () VAR _AsAt = IF ( _IsDateFiltered, _Filtered, _Unfiltered ) RETURN _AsAt
The time intelligence measure expression then calculates the financial year to date value of the selected measure, which for closed cases is # Cases Closed. The expression for the # Cases Closed measure is substituted into the Time Intelligence Calculation Item and the result of the value displayed in the Cases closed card visual is therefore calculated as follows:
# Cases Closed Financial Year To Date := VAR _AsAtDate = [As At Date] VAR _FinStartOfYear = LOOKUPVALUE ( 'Date'[Financial StartOfYear], 'Date'[Date], _AsAtDate ) VAR _Result = CALCULATE ( -- The [# Cases Closed] measure is substituted here CALCULATE ( COUNTROWS ( 'Case Granularity' ), USERELATIONSHIP ( 'Case Granularity'[CaseClosedDateKey], 'Date'[DateKey] ) ), 'Date'[Date] >= _FinStartOfYear, 'Date'[Date] <= _AsAtDate ) RETURN _Result