4C. Configure Power BI report filters

Question 1

What are the five levels at which filtering can occur in a Power BI report?

Answer 1

• Semantic model (RLS)
• Report
• Page
• Visual
• Measure

Report, page, and visual level filters apply to the structure of the report.

Question 2

How do reports relate to RLS filtering?

Answer 2

Every Power BI report queries a single semantic model, which is a Power BI artifact that represents a semantic model. The semantic model can enforce row-level security (RLS) to restrict access to a subset of data, and different users will see different data. A report can’t determine whether the semantic model will enforce RLS, and it can’t override RLS.

Question 3

What should you keep in mind about the user experience of reports in relation to RSL filtering?

Answer 3

When you filter RLS-filtered tables, take care to avoid situations where some report consumers see no data (BLANK) in the report. For example, consider a model that enforces RLS to restrict data visibility to specific countries/regions. If at design time, you have permission to see Australian data and you filter the report by Australia, a report consumer that has permission to see only United States data won’t see data at all.

Question 4

When can you not create measures when designing a report?

Answer 4

When the model is a live connection to SQL Server Analysis Services multidimensional model.

Question 5

How do using CALCULATE or CALCULATETABLE in a measure interact with report structure filter context?

Answer 5

Measure formulas, which are written in Data Analysis Expressions (DAX), can modify filter context by using the CALCULATE or CALCULATETABLE functions. These functions are powerful and provide you with the flexibility to add, remove, or modify filters. A set of DAX functions, known as time intelligence functions, also modify filter context. These functions can override any filters that are applied to the report structure.

A good example of a measure that overrides report filters is a three-month moving average calculation. To compute the result for March, the filter context for month must expand to encompass January, February, and March. The CALCULATE function or a time intelligence function can modify the filter context to produce that result.

Question 6

How can measures filter visuals?

Answer 6

Unlike report and page-level filters, a visual-level filter can filter by using a measure. When a measure filters a visual, it’s used to eliminate groups. For example, consider a column chart visual that groups by store. A measure filter could eliminate groups (stores) where the total store sales are less than a certain amount.

Question 7

What are the four different filter types/options?

Answer 7

• Basic
• Advanced
• Top N
• Relative date and Relative time

Question 8

What does the Basic filter do?

Answer 8

The Basic filter type allows you to select items from a list of distinct values that are found in the field. You can also restrict the filter to a single selection instead of multi-selection.

Question 9

What does the Advanced filter do?

Answer 9

The Advanced filter type allows you to create more complex conditions by using data type-specific operators:

• Text field operators - Test for conditions such as “contains,” “starts with,” “is blank,” “is empty,” and others.
• Numeric field operators - Test for conditions such as “is less than,” “is less than or equal to,” and others.
• Date field operators - Test for conditions such as “is after,” “is on or after,” and others.

You can combine multiple tests by using a logical AND/OR operator

Question 10

What does the Top N filter do?

Answer 10

The Top N filter type applies to text and date fields that are available only in visual-level filters. This filter type helps you filter by the top (or bottom) number of items, like the top five products by revenue. To configure the filter, you must pass in a field that’s summarized, like sales revenue.

Question 11

What does the Relative date and Relative time filter do?

Answer 11

The Relative date and Relative time filter types apply to date fields only, making it possible to filter by relative date or time. Relative filters allow the report consumer to filter by past, present, or future time periods based on the current date and time.

Question 12

Why might you want to lock filters?

Answer 12

It’s a good idea to lock filters that are critical to the design of the report, page, or visual.

Question 13

Why might you want to hide filters?

Answer 13

A hidden filter isn’t visible to report consumers. Consider hiding a filter when the report consumer doesn’t need to know about it, such as when filters are cleaning up the data, perhaps by removing BLANKs.

Question 14

What is a slicer?

Answer 14

The slicer is a core visual with one purpose: filter other visuals.

Question 15

What are the default and optional behaviours of slicers?

Answer 15

• By default, slicers filter all other visuals on the page.
• You can edit visual interactions to restrict filtering between two visuals.
• Sync slicers can also filter visuals on other pages.
• You can configure a slicer by using one or more fields from the same table or a hierarchy. When configured to use multiple fields or a hierarchy, the slicer presents an expandable tree structure of items.

It might be tempting to think that slicers apply page-level filters because that’s the default result. However, it’s important to understand that a slicer is a visual that propagates filters to other visuals on the same page or (when synced) across other pages.

Question 16

How do slicers interact with data types?

Answer 16

The slicer layout is responsive to the data type of the field. Field data types are either text, numeric, or date. By default, - a text field will produce a list slicer
- a numeric field will produce a numeric range “between” filter
- a date field will produce a date range “between” filter, allowing value selection with calendar controls.

At design time, you can modify the slicer layout so that lists become dropdown lists. Dropdown lists use much less space on the report page. Numeric and date ranges offer additional layouts, allowing you to select a single value that acts as the lower or upper boundary of the filter. The reason why numeric and date slicers have additional layouts is because these data types represent continuous values. Therefore, the slicer layouts allow filtering by ranges of continuous values.

Question 17

What are the possible layouts for date slicers

Answer 17

• Text field - List (default) or dropdown
• Numeric field - List, dropdown, between (default), less than or equal to, or greater than or equal to
• Date field - List, dropdown, between (default), before, after, relative date, or relative time

Relative filters allow the report consumer to filter by past, present, or future time periods based on the current date and time. For example, a relative date slicer could filter by the current date (today).

Question 18

What are some benefits of dropdown lists compare to lists?

Answer 18

They help minimize the size of the slicer to provide more room on the page for other visuals. An additional benefit when using dropdown slicers that might not be immediately apparent is that they only query the semantic model when expanded open. Therefore, they can also help expedite report page rendering.

Question 19

What are ways of controlling the selection of items in slicers?

Answer 19

List and dropdown slicers support format options to control the selection of items. When you enable the Single select option, the slicer allows only a single item selection. That approach makes sense for a slicer like scenario, where the options are Actual, Budget, or Forecast. In this case, it only makes sense to filter by one scenario at a time.

Question 20

What are advanced ways of filtering, beyond using filters and slicers?

Answer 20

• Visual interactions
• Drillthrough
• Report tooltip
• Bookmarks
• Report options
• Query reduction options

Question 21

What are visual interactions?

Answer 21

• By default, when report consumers interact with visuals, filters are propagated to other visuals on the report page. This way, visuals behave like slicers.
• Cross filtering (and cross highlighting) works between any two visuals.
• However, at design time, you can edit visual interactions between any pair of visuals and in either direction. Additionally, you can disable cross filtering or modify the interaction to use cross highlighting instead.

Question 22

What is drillthrough, and how does it interact with filters?

Answer 22

• Add drillthrough pages to allow report consumers to drill from visuals.
• By default, the drillthrough action propagates all filters that apply to the visual to the drillthrough page.

Question 23

What are tooltips and how do they interact with filters?

Answer 23

• Add report tooltips that will appear when report consumers hover the cursor over visuals.
• By default, the report tooltip receives all filters that apply to the visual.

Question 24

What are bookmarks, and what are some ways of using them?

Answer 24

• Bookmarks capture a specific view of a report, including filters, slicers, the page selection, and the state of visuals. Report authors and report consumers can create them.
• Because bookmarks can capture filter state, when invoked, they will apply that filter state. You can invoke bookmarks directly from the Bookmarks pane, or you can invoke them indirectly by selecting a button, image, or shape.
• You can create a bookmark to capture the default state of slicers. A button on the report page could have the text Reset slicers, and when invoked, it will use the bookmark.

Question 25

What report options can you configure that will affect filtering, and what should you consider before changing the default settings here?

Answer 25

• Disable persistent filters.
• Hide visual headers for all visuals or for a specific visual. When the visual header is hidden, report consumers can’t hover the cursor over the Filter icon to determine the filters that are applied to the visual. (The Filter icon is covered in Unit 5.)
• Hide the Filter icon for a specific visual.
• Restrict report consumers from changing filter types (for example, basic to advanced) in the Filters pane.
• Remove the search box in the Filters pane.

The default settings provide the most flexibility for report consumers. Only restrict options when you have a compelling reason to do so.

Question 26

What are “personal bookmarks?”

Answer 26

Bookmarks that are created by a report consumer.

Question 27

What are some query reduction options you can configure that affect filters/slicers, and that will improve performance of the report?

Answer 27

• Disable cross highlighting/filtering by default. All visuals, except for slicers, don’t propagate filters to other visuals unless they are explicitly added as a visual interaction.
• Add an Apply button to slicers. The slicer selection will only filter the report page when the report consumer selects the Apply button.
• Add an Apply button to all basic filters in the Filters pane. A single filter will apply when the report consumer selects the Apply button in the filter card.
• Add an Apply button to the Filters pane. All filters will apply simultaneously when the report consumer selects the Apply button. This option is helpful when report consumers want to update many filters at the same time.

Question 28

When should you consider enabling query reduction options to improve performance?

Answer 28

Consider enabling the query reduction options when the semantic model uses DirectQuery table storage or when imported data volumes are large and calculations are complex and slow.

Question 29

How can report consumers filter reports?

Answer 29

• Using slicers.
• Using filters.
• Applying interactive filtering actions.
• Determining applied filters.
• Working with persistent filters.

Question 30

How can report consumers use slicers?

Answer 30

Slicers provide an intuitive experience for report consumers. They are potentially the most-used interactive technique in Power BI reports. Report consumers can modify or clear slicer filters. They can also enable the search feature, allowing them to quickly locate specific items to filter by.

Question 31

How can and can’t report consumers modify filters?

Answer 31

They can:
- Select the eraser icon to clear the filter.
- Apply a new filter selection.
- Change the filter type, such as from basic to advanced (unless disabled for the report).
- Use the search box to search for values to filter by (unless disabled for the report).

They can’t:
- Add new filters.
- Remove filters.
- Change the filter type to Top N, or relative date or time (for date/time fields).
- Enforce or disable single selection.

Question 32

What interactive filtering actions can report consumers take?

Answer 32

• Include/exclude groups
• Cross filter
• Report page drillthrough (which propagates all page filter to the drillthrough by default)
• Bookmarks

Question 33

How can report consumers determine applied filters?

Answer 33

Occasionally, report consumers want to know (or verify) what filters, if any, apply to a specific visual. They can complete that action by hovering the cursor over the Filter icon in the visual header. A pop-up window will appear, describing the filters that affect the visual. The pop-up window shows the accumulation of filters that are applied by slicers or filters, including any cross filters.

If the Filter icon isn’t available for a visual, it could be because:
- The report settings disable visual headers for the entire report.
- The visual settings disable the visual header for the visual.
- The visual settings disable the visual header Filter icon for the visual.

Question 34

What are persistent filters?

Answer 34

Persistent filters is a feature that saves report consumer’s slicer and filter settings. It automatically applies the settings when the report consumer reopens the report. That way, Power BI remembers previously applied filters. You can revert to default filters (that are saved in the report by the report author) by selecting Reset to default.

If the Reset to default button is not available, it could be because persistent filters is disabled for the report. This button will also reset the state of visuals, including drillthrough location and sorting.

Question 35

What are advantages of filters (as opposed to slicers)?

Answer 35

• The Filters pane is consistently located on the right side of the report.
• It allows you to configure advanced filter types, like Top N, or allows you to use more complex expressions, like “contains,” “does not contain,” “is blank,” and others.
• The search box and sort functions (to order filters within the sections) are powerful tools to use when many filters exist.
• Filters result in faster report rendering because no visual rendering is required.
• You can lock and/or hide filters.
• When the Apply button is enabled, you can submit all filter updates at once, resulting in fewer queries.
• You can filter by using measures (only at visual level).

Question 36

What are disadvantages of filters (as opposed to slicers)?

Answer 36

• The Filters pane offers less design flexibility, though you can style it by using specific fonts and colors.
• Careful consideration should be made to hide certain filters to avoid confusing report consumers.
• It can be easy to lose track of applied visual-level filters.

Question 37

What are advantages of slicers (as opposed to filters)?

Answer 37

• You can place slicers anywhere on the report page to produce an intuitive layout.
• Slicers are highly configurable to achieve the required functionality and style.
• You can configure hierarchical slicers (based on a hierarchy or by using multiple fields that are sourced from the same table).
• They can display their selection (filter context) directly on the report page.
• They can display images when you are using a field that is categorized as an image URL. For more information, see Specify data categories in Power BI Desktop.
• They can filter only certain visuals by editing visual interactions.
• You can filter slicers as you would any visual. For example, you can apply a filter to a slicer to remove the BLANK item.
• You can sort slicer items.
• Synced slicers can filter other pages in the report.

Question 38

What are disadvantages of slicers (as opposed to filters)?

Answer 38

• Slicers can have an impact on report render performance due to the visual render time.
• They occupy space on the page that could be used by data visuals.
• Slicer filtering options are less complex than the advanced filter options that are possible in the Filters pane. For example, you can’t configure a Top N slicer.

Question 39

Which are better for report performance, filters or slicers?

Answer 39

• Filters result in faster report rendering because no visual rendering is required.
• When the Apply button is enabled, you can submit all filter updates at once, resulting in fewer queries.

Question 40

What are some tips relating to slicers and filters for producing successful report designs?

Answer 40

• Use either filters or slicers. Avoid using both filter techniques because it can create confusion.
• In the Filters pane, consider locking or hiding visual-level filters to avoid confusing report consumers. (Often, report consumer shouldn’t modify or see visual-level filters.)
• Create a bookmark to reset all slicers to default values. Then, add a button to the page to invoke the bookmark. For example, the button could be captioned as Reset slicers.
• When a requirement is in place to lay out many slicers, consider creating a page that is dedicated to showing all slicers. For example, the page could be named Slicers. Sync the slicers to other pages and then set the slicers as hidden on those pages. This design technique will require that report consumers should always go to the Slicers page to modify slicer settings. To help them, you can add a page navigation button at a consistent location on each page so that they can easily return to the Slicers page.
• Consider using other visuals in place of slicers. Be sure to teach report consumers how to cross filter by using these visuals.

Question 41

What orientations are available for list slicers, and what should you remember about them?

Answer 41

• Vertical
• Horizontal

Take care not to use horizontal orientation when many distinct values are present. Often, report consumers don’t notice the scroll arrows that allow navigation to the following items. Consequently, they might think that the value they’re looking for doesn’t exist.