Slicers, Filters and Visual Calculations

What it is

Readers narrow data with slicers on the canvas and with filters in the Filters pane. Authors choose between them based on who needs control. Visual calculations are a newer feature: DAX written directly on a visual, using only the data in that visual, for things like running totals and percent of parent.

How it works

Slicers

A slicer is a visual that filters other visuals on the page. Types depend on the field.

Settings include single select or multi select, Select all, a search box and a Slicer header. A slicer filters every visual on the page by default. You can stop a slicer affecting a visual with Edit interactions, and apply a slicer to other pages with Sync slicers, both covered later.

The Filters pane

The pane has three levels.

Filter types are Basic (tick values), Advanced (conditions such as contains or starts with, with And or Or), Top N (top or bottom items by a measure), Relative date and Relative time. Filters can be locked or hidden by the author, so readers cannot change them.

Slicer or filter? A slicer is visible and designed for readers to interact with. A filter in the pane is suited to fixed conditions the author sets, such as excluding test data, and to filters that readers rarely change. Page level filters and slicers combine with each other, and all must be satisfied.

Visual calculations

A visual calculation is a DAX expression defined and run on a visual, not stored in the model. It refers to what is in the visual: columns, measures or other visual calculations. Because it works on the aggregated rows already in the visual, it is often simpler and faster than an equivalent measure.

To add one, select the visual and choose New calculation, which opens Edit mode with a preview, a formula bar and a visual matrix. After you save it, it appears as a field in the visual.

Running sum = RUNNINGSUM ( [Sales Amount] )
Moving average = MOVINGAVERAGE ( [Sales Amount], 3 )
Percent of parent = DIVIDE ( [Sales Amount], COLLAPSE ( [Sales Amount], ROWS ) )
Versus previous = [Sales Amount] - PREVIOUS ( [Sales Amount] )

Many have optional Axis (ROWS or COLUMNS, and so on) and Reset parameters. The axis sets the direction of travel across the visual, and reset restarts a running calculation, for example each year. Templates such as Running sum, Moving average and Percent of parent write the formula for you.

Rules to remember:

• A visual calculation can use only fields in the visual. Add the field first, and hide it if you do not want it displayed.
• It cannot use functions that need model relationships, such as USERELATIONSHIP, RELATED and RELATEDTABLE.
• It is not reusable across visuals, cannot be pinned to a dashboard and does not work in some visuals such as slicers and the Key influencers visual.
• It is not part of the model, so other reports cannot see it.

Visual calculation or measure

A worked scenario

An author wants a column chart of monthly sales with a line showing the running total for the year. She selects the visual, uses the Running sum template on Sales Amount with Reset to the year, and hides nothing. Another visual needs the sales share of each category within its department, so she uses Percent of parent with COLLAPSE. A measure for gross margin is used on many pages, so she writes that one in the model.