Using Sync slicers
Pam wants the slicers in the CONTROLS area to stay in sync across the pages of the report. This means that when a slicer is changed or adjusted on one page, all of the other pages of the report should reflect this change. To use the sync slicers feature, do the following:
- Start on the Template page.
- Click on the View tab of the ribbon. In the Show panes section, choose Sync slicers. The Sync slicers pane will appear between the Filters pane and the Visualizations pane.
- Select the Dates slicer. Note that, at the top, all of the page names in the report are listed – in this case, just Template. The visible (eyeball) icon indicates whether or not the slicer is present on the page. There is also a sync icon to the left of the visible icon that allows you to check on which pages the slicer keeps in sync.
-
Expand the Advanced options section.
In the field, type Dates and then note that the checkboxes next to Sync field changes to other slicers and Sync filter changes to other slicers are checked automatically, as shown in the following screenshot. These options mean that any slicers with this same group name will be kept in sync, regardless of the pages they are on:
Figure 7.10 – Sync slicers pane
- Click on the Target Utilization slicer and add a group name of Target Utilization.
- Click on the Branch slicer and add a group name of Branches.
- Click on the Division slicer and add a group name of Divisions.
- Click on the Employee Type slicer and add a group name of Employee Types.
Pam is now done with the Sync slicers pane, so unselect the Sync slicers icon on the View
tab of the ribbon.
Adjusting the calendar
Pam only wants dates that are contained within the report to be displayed in the Dates slicer. When Pam created the Calendar table, she used the CALENDAR function and used 1/1/2017 and 12/31/2019 as the beginning and end dates for the CALENDAR function’s parameters, respectively. This means that the Dates slicer displays all of these dates. This will not do for the final report.
To fix this, click on the Calendar table and edit the DAX formula definition so that it’s now as follows:
Calendar = CALENDAR( MIN(Hours[Date]), MAX(Hours[Date]) )
This formula replaces the hardcoded date values with the minimum and maximum date values in the Hours table. The Dates slicer now only has available dates of 1/1/2019
to 3/22/2019. Pam knows that when she adds data to the report, this table will be recalculated during the refresh, so the report’s Dates slicer will always only have the dates that are available within the data model.
The CALENDARAUTO function can also help ensure that all of the calendar dates within the model are part of the calendar table. CALENDARAUTO is similar to CALENDAR
in that it creates a table of dates, but CALENDARAUTO analyzes the data model and creates the calendar table between the minimum and maximum dates within the entire model. To control exactly what dates appear more accurately in your calendar/date table, it is recommended to use the CALENDAR function, as described here, instead of CALENDARAUTO.
Adding report filters
As a final preparation step, Pam wants to add overall report filters. Report-level filters filter all of the pages of the report, which will save time compared to placing these same filters on each page.
Pam knows that, for this report, management only cares about billable resources and not about back-office resources such as administrators, human resources, sales, and so on.
Pam already created a group that groups billable resources.
To implement this report filter, perform the following steps:
- Expand the Fields pane and then the People table.
-
Right-click EmployeeType (groups), choose Add to filters, and then choose
Report-level filters.
-
Expand the Filters pane and in the Filters on all pages section, find
EmployeeType (groups).
-
Ensure that Filter type is set to Basic filtering. Then, in the Values area, check Billable.
The Employee Type slicer now only lists HOURLY, SALARY, and
SUB-CONTRACTOR.
Similarly, Pam only wants to focus this report on the three main lines of consulting services the company provides; that is, technology, accounting, and management consulting.
To add this filter, do the following:
-
Expand the Hours table in the Fields pane and add Division (groups) (not the Division column or Division Hierarchy) to the Filters on all pages area of the Filters pane.
- Using Basic filtering, select Technology, Accounting, and Management. The Division slicer now only shows the values for these three groups.
We are now done preparing for the creation of our final report pages. So, let’s get to it!
